<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>深入浅出学习Hive | 全栈进阶那些事</title><meta name="keywords" content="Hadoop,Hive"><meta name="author" content="liuyuantao"><meta name="copyright" content="liuyuantao"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="本文是基于CentOS 7.9系统环境，进行hive的学习和使用  一、Hive的简介1.1 Hive基本概念(1) 什么是hive Hive是用于解决海量结构化日志的数据统计工具，是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能(2) Hive的本质 Hive的本质就是将HQL转化成MapReduce程序 1.2 Hive优缺点(1) 优点">
<meta property="og:type" content="article">
<meta property="og:title" content="深入浅出学习Hive">
<meta property="og:url" content="https://liuyuantao.gitee.io/2021/11/13/3f0a31ee0039.html">
<meta property="og:site_name" content="全栈进阶那些事">
<meta property="og:description" content="本文是基于CentOS 7.9系统环境，进行hive的学习和使用  一、Hive的简介1.1 Hive基本概念(1) 什么是hive Hive是用于解决海量结构化日志的数据统计工具，是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能(2) Hive的本质 Hive的本质就是将HQL转化成MapReduce程序 1.2 Hive优缺点(1) 优点">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://img-blog.csdnimg.cn/20200529093005305.png">
<meta property="article:published_time" content="2021-11-13T00:36:02.000Z">
<meta property="article:modified_time" content="2021-11-13T09:45:35.631Z">
<meta property="article:author" content="liuyuantao">
<meta property="article:tag" content="Hadoop">
<meta property="article:tag" content="Hive">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://img-blog.csdnimg.cn/20200529093005305.png"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="https://liuyuantao.gitee.io/2021/11/13/3f0a31ee0039"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//hm.baidu.com"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><script>var _hmt = _hmt || [];
(function() {
  var hm = document.createElement("script");
  hm.src = "https://hm.baidu.com/hm.js?d43383137144f76a37f1074576bc674e";
  var s = document.getElementsByTagName("script")[0]; 
  s.parentNode.insertBefore(hm, s);
})();
</script><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: {"defaultEncoding":1,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"简"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":200},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: true,
    post: true
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":500,"languages":{"author":"作者: liuyuantao","link":"链接: ","source":"来源: 全栈进阶那些事","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#2d3035","position":"top-right"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: true,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: '深入浅出学习Hive',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-11-13 17:45:35'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    
    const detectApple = () => {
      if (GLOBAL_CONFIG_SITE.isHome && /iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 5.4.0"><link rel="alternate" href="/atom.xml" title="全栈进阶那些事" type="application/atom+xml">
</head><body><div id="loading-box"><div class="loading-left-bg"></div><div class="loading-right-bg"></div><div class="spinner-box"><div class="configure-border-1"><div class="configure-core"></div></div><div class="configure-border-2"><div class="configure-core"></div></div><div class="loading-word">加载中...</div></div></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/img/zhiyao_avatar.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">41</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">14</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">4</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">全栈进阶那些事</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">深入浅出学习Hive</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="fa-fw post-meta-icon far fa-calendar-alt"></i><span class="post-meta-label">发表于</span><time datetime="2021-11-13T00:36:02.000Z" title="undefined 2021-11-13 08:36:02">2021-11-13</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E5%A4%A7%E6%95%B0%E6%8D%AE/">大数据</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">11.5k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>48分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="深入浅出学习Hive"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div><article class="post-content" id="article-container"><blockquote>
<p>本文是基于CentOS 7.9系统环境，进行hive的学习和使用</p>
</blockquote>
<h1 id="一、Hive的简介"><a href="#一、Hive的简介" class="headerlink" title="一、Hive的简介"></a>一、Hive的简介</h1><h2 id="1-1-Hive基本概念"><a href="#1-1-Hive基本概念" class="headerlink" title="1.1 Hive基本概念"></a>1.1 Hive基本概念</h2><p><strong>(1) 什么是hive</strong></p>
<p>Hive是用于解决海量结构化日志的数据统计工具，是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能<br><strong>(2) Hive的本质</strong></p>
<p>Hive的本质就是将HQL转化成MapReduce程序</p>
<h2 id="1-2-Hive优缺点"><a href="#1-2-Hive优缺点" class="headerlink" title="1.2 Hive优缺点"></a>1.2 Hive优缺点</h2><p><strong>(1) 优点</strong></p>
<ol>
<li> 操作接口采用类SQL语法，提供快速开发的能力（简单、容易）</li>
<li> 避免写MapReduce程序，减少开发人员的学习成本</li>
<li> Hive优势在于处理大数据，常用于数据分析，适用于实时性要求不高的场景</li>
<li> hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数</li>
</ol>
<p><strong>(2) 缺点</strong></p>
<ol>
<li> Hive执行延迟比较高，对于处理小数据没有优势</li>
<li> hive的HQL表达能力有限（迭代式算法无法表达；数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现）</li>
<li> hive的效率比较低（hive自动生成的MapReduce作业，通常情况下不够智能化；hive调优比较困难，粒度较粗）</li>
</ol>
<h2 id="1-3-Hive架构"><a href="#1-3-Hive架构" class="headerlink" title="1.3 Hive架构"></a>1.3 Hive架构</h2><p><img src="https://img-blog.csdnimg.cn/20200529093005305.png"></p>
<ul>
<li>Client 用户接口  CLI（command-line interface）、JDBC/ODBC(jdbc访问hive)、WEBUI（浏览器访问hive）</li>
<li>Metastore  元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore</li>
<li>SQL Parser 解析器  对SQL语句进行解析，转换成抽象语法树AST，并进行语法分析和检查</li>
<li>Physical Plan 编译器  将抽象语法树AST编译成逻辑执行计划</li>
<li>Query Optimizer 优化器  对逻辑执行计划进行优化</li>
<li>Execution 执行器  将逻辑执行计划转换成可以运行的物理计划，也就是MR任务</li>
</ul>
<h2 id="1-4-Hive工作机制"><a href="#1-4-Hive工作机制" class="headerlink" title="1.4 Hive工作机制"></a>1.4 Hive工作机制</h2><p>Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。<br><img src="https://img-blog.csdnimg.cn/20200529133302731.png"></p>
<h2 id="1-5-Hive和数据库比较"><a href="#1-5-Hive和数据库比较" class="headerlink" title="1.5 Hive和数据库比较"></a>1.5 Hive和数据库比较</h2><p>由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。</p>
<ul>
<li>查询语言  由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。</li>
<li>数据存储位置  Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。</li>
<li>数据更新  由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。</li>
<li>执行  Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。</li>
<li>执行延迟  Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。</li>
<li>可扩展性  由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的（世界上最大的Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。</li>
<li>数据规模  由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。</li>
</ul>
<h1 id="二、Hive的安装"><a href="#二、Hive的安装" class="headerlink" title="二、Hive的安装"></a>二、Hive的安装</h1><h2 id="2-1-Hive下载"><a href="#2-1-Hive下载" class="headerlink" title="2.1 Hive下载"></a>2.1 Hive下载</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">apache-hive-1.2.1-bin.tar.gz</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-2-Hive解压"><a href="#2-2-Hive解压" class="headerlink" title="2.2 Hive解压"></a>2.2 Hive解压</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">tar -xzvf apache-hive-1.2.1-bin.tar.gz -C /opt/module</span><br><span class="line"><span class="built_in">cd</span> /opt/module</span><br><span class="line">mv apache-hive-1.2.1-bin hive</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-3-配置环境变量"><a href="#2-3-配置环境变量" class="headerlink" title="2.3 配置环境变量"></a>2.3 配置环境变量</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi /etc/profile</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line"><span class="comment">#HIVE_HOME</span></span><br><span class="line"><span class="built_in">export</span> HIVE_HOME=/opt/module/hive</span><br><span class="line"><span class="built_in">export</span> PATH=<span class="variable">$PATH</span>:<span class="variable">$HIVE_HOME</span>/bin</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>2.4 修改hive配置文件</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/conf</span><br><span class="line">cp hive-env.sh.template hive-env.sh</span><br><span class="line">vi hive-env.sh</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line"><span class="comment"># Set HADOOP_HOME to point to a specific hadoop install directory</span></span><br><span class="line">HADOOP_HOME=/opt/module/hadoop-2.7.2</span><br><span class="line"><span class="comment"># Hive Configuration Directory can be controlled by:</span></span><br><span class="line"><span class="built_in">export</span> HIVE_CONF_DIR=/opt/module/hive/conf</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-5-启动并测试hive"><a href="#2-5-启动并测试hive" class="headerlink" title="2.5 启动并测试hive"></a>2.5 启动并测试hive</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 创建数据库</span></span><br><span class="line">create database <span class="built_in">test</span>;</span><br><span class="line"><span class="comment"># 创建数据表</span></span><br><span class="line">create table student(id int, name string);</span><br><span class="line"><span class="comment"># 插入数据</span></span><br><span class="line">insert into table student values(1001, <span class="string">&quot;zhangsan&quot;</span>);</span><br><span class="line"><span class="comment"># 查询数据</span></span><br><span class="line">select * from student;</span><br><span class="line"><span class="comment"># 删除数据表</span></span><br><span class="line">drop table student;</span><br><span class="line"><span class="comment"># 删除数据库</span></span><br><span class="line">drop database <span class="built_in">test</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-6-hive的bug"><a href="#2-6-hive的bug" class="headerlink" title="2.6 hive的bug"></a>2.6 hive的bug</h2><p>hive默认存储元数据的数据库为derby，不支持并发访问，多开几个hive客户端会出现异常</p>
<h2 id="2-7-MySQL的安装"><a href="#2-7-MySQL的安装" class="headerlink" title="2.7 MySQL的安装"></a>2.7 MySQL的安装</h2><p>hive默认存储元数据的数据库为derby，不支持并发访问，多开几个hive客户端会出现异常，因此需要安装MySQL数据库来替换</p>
<p>CentOS 7离线安装MySQL 5.6</p>
<h2 id="2-8-Hive配置MySQL"><a href="#2-8-Hive配置MySQL" class="headerlink" title="2.8 Hive配置MySQL"></a>2.8 Hive配置MySQL</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/conf</span><br><span class="line">vi hive-site.xml</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">&lt;?xml version=<span class="string">&quot;1.0&quot;</span>?&gt;</span><br><span class="line">&lt;?xml-stylesheet <span class="built_in">type</span>=<span class="string">&quot;text/xsl&quot;</span> href=<span class="string">&quot;configuration.xsl&quot;</span>?&gt;</span><br><span class="line">&lt;configuration&gt;</span><br><span class="line">    &lt;property&gt;</span><br><span class="line">      &lt;name&gt;javax.jdo.option.ConnectionURL&lt;/name&gt;</span><br><span class="line">      &lt;value&gt;jdbc:mysql://192.168.1.101:3306/metastore?createDatabaseIfNotExist=<span class="literal">true</span>&lt;/value&gt;</span><br><span class="line">      &lt;description&gt;JDBC connect string <span class="keyword">for</span> a JDBC metastore&lt;/description&gt;</span><br><span class="line">    &lt;/property&gt;</span><br><span class="line"></span><br><span class="line">    &lt;property&gt;</span><br><span class="line">      &lt;name&gt;javax.jdo.option.ConnectionDriverName&lt;/name&gt;</span><br><span class="line">      &lt;value&gt;com.mysql.jdbc.Driver&lt;/value&gt;</span><br><span class="line">      &lt;description&gt;Driver class name <span class="keyword">for</span> a JDBC metastore&lt;/description&gt;</span><br><span class="line">    &lt;/property&gt;</span><br><span class="line"></span><br><span class="line">    &lt;property&gt;</span><br><span class="line">      &lt;name&gt;javax.jdo.option.ConnectionUserName&lt;/name&gt;</span><br><span class="line">      &lt;value&gt;root&lt;/value&gt;</span><br><span class="line">      &lt;description&gt;username to use against metastore database&lt;/description&gt;</span><br><span class="line">    &lt;/property&gt;</span><br><span class="line"></span><br><span class="line">    &lt;property&gt;</span><br><span class="line">      &lt;name&gt;javax.jdo.option.ConnectionPassword&lt;/name&gt;</span><br><span class="line">      &lt;value&gt;123456&lt;/value&gt;</span><br><span class="line">      &lt;description&gt;password to use against metastore database&lt;/description&gt;</span><br><span class="line">    &lt;/property&gt;</span><br><span class="line">&lt;/configuration&gt;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-9-启动Hive"><a href="#2-9-启动Hive" class="headerlink" title="2.9 启动Hive"></a>2.9 启动Hive</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="2-10-Beeline启动Hive"><a href="#2-10-Beeline启动Hive" class="headerlink" title="2.10 Beeline启动Hive"></a>2.10 Beeline启动Hive</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/bin</span><br><span class="line">./hiveserver2</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>打开另一个终端</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/bin</span><br><span class="line">./beeline</span><br><span class="line">!connect jdbc:hive2://hadoop101:10000</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>只需输入启动hadoop的用户名，不需要密码</p>
<h1 id="三、Hive的使用"><a href="#三、Hive的使用" class="headerlink" title="三、Hive的使用"></a>三、Hive的使用</h1><h2 id="3-1-Hive的交互命令"><a href="#3-1-Hive的交互命令" class="headerlink" title="3.1 Hive的交互命令"></a>3.1 Hive的交互命令</h2><p>运行来自命令行的SQL</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive</span><br><span class="line">bin/hive -e <span class="string">&quot;select * from test.student;&quot;</span></span><br><span class="line">bin/hive -e <span class="string">&quot;select * from test.student;&quot;</span>&gt;result.log</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>运行来自文件的SQL</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive</span><br><span class="line">vi test.sql</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">select * from test.student;</span><br><span class="line"><span class="comment"># 执行下面命令</span></span><br><span class="line">bin/hive -f test.sql&gt;result.log</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>退出hive客户端</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">quit;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="3-2-Hive数据仓库位置配置"><a href="#3-2-Hive数据仓库位置配置" class="headerlink" title="3.2 Hive数据仓库位置配置"></a>3.2 Hive数据仓库位置配置</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/conf</span><br><span class="line">vi hive-site.xml</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">&lt;property&gt;</span><br><span class="line">    &lt;name&gt;hive.metastore.warehouse.dir&lt;/name&gt;</span><br><span class="line">    &lt;value&gt;/user/hive/warehouse&lt;/value&gt;</span><br><span class="line">    &lt;description&gt;location of default database <span class="keyword">for</span> the warehouse&lt;/description&gt;</span><br><span class="line">&lt;/property&gt;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="3-3-查询后信息显示配置（可选配）"><a href="#3-3-查询后信息显示配置（可选配）" class="headerlink" title="3.3 查询后信息显示配置（可选配）"></a>3.3 查询后信息显示配置（可选配）</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/conf</span><br><span class="line">vi hive-site.xml</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">&lt;property&gt;</span><br><span class="line">    &lt;name&gt;hive.cli.print.header&lt;/name&gt;</span><br><span class="line">    &lt;value&gt;<span class="literal">true</span>&lt;/value&gt;</span><br><span class="line">&lt;/property&gt;</span><br><span class="line">&lt;property&gt;</span><br><span class="line">    &lt;name&gt;hive.cli.print.current.db&lt;/name&gt;</span><br><span class="line">    &lt;value&gt;<span class="literal">true</span>&lt;/value&gt;</span><br><span class="line">&lt;/property&gt;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="3-4-Hive运行日志信息配置（必须配置）"><a href="#3-4-Hive运行日志信息配置（必须配置）" class="headerlink" title="3.4 Hive运行日志信息配置（必须配置）"></a>3.4 Hive运行日志信息配置（必须配置）</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">cd</span> /opt/module/hive/conf</span><br><span class="line">cp hive-log4j.properties.template hive-log4j.properties</span><br><span class="line">vi hive-log4j.properties</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">hive.log.dir=/opt/module/hive/logs</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="四、Hive的数据类型"><a href="#四、Hive的数据类型" class="headerlink" title="四、Hive的数据类型"></a>四、Hive的数据类型</h1><h2 id="4-1-Hive基本数据类型"><a href="#4-1-Hive基本数据类型" class="headerlink" title="4.1 Hive基本数据类型"></a>4.1 Hive基本数据类型</h2><table>
<thead>
<tr>
<th>hive数据类型</th>
<th>java数据类型</th>
<th>长度</th>
<th>示例</th>
</tr>
</thead>
<tbody><tr>
<td>tinyint</td>
<td>byte</td>
<td>1byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>smalint</td>
<td>short</td>
<td>2byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>int</td>
<td>int</td>
<td>4byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>bigint</td>
<td>long</td>
<td>8byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>boolean</td>
<td>boolean</td>
<td>布尔类型，true或者false</td>
<td>TRUE FALSE</td>
</tr>
<tr>
<td>float</td>
<td>float</td>
<td>单精度浮点数</td>
<td>3.14159</td>
</tr>
<tr>
<td>double</td>
<td>double</td>
<td>双精度浮点数</td>
<td>3.14159</td>
</tr>
<tr>
<td>string</td>
<td>string</td>
<td>字符系列，可以使用单引号或者双引号</td>
<td>‘now is’ “i am a”</td>
</tr>
<tr>
<td>timestamp</td>
<td></td>
<td>时间类型</td>
<td></td>
</tr>
<tr>
<td>binary</td>
<td></td>
<td>字节数组</td>
<td></td>
</tr>
</tbody></table>
<h2 id="4-2-Hive集合数据类型"><a href="#4-2-Hive集合数据类型" class="headerlink" title="4.2 Hive集合数据类型"></a>4.2 Hive集合数据类型</h2><table>
<thead>
<tr>
<th>数据类型</th>
<th>描述</th>
<th>语法示例</th>
</tr>
</thead>
<tbody><tr>
<td>struct</td>
<td>和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。</td>
<td>struct() 例如struct&lt;street:string, city:string&gt;</td>
</tr>
<tr>
<td>map</td>
<td>MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键-&gt;值对是’first’-&gt;’John’和’last’-&gt;’Doe’，那么可以通过字段名[‘last’]获取最后一个元素</td>
<td>map() 例如map&lt;string, int&gt;</td>
</tr>
<tr>
<td>array</td>
<td>数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。</td>
<td>Array() 例如array</td>
</tr>
</tbody></table>
<ul>
<li>案例  创建数据文件test.txt</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi test.txt</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing</span><br><span class="line">yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>创建表结构文件test.sql</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi test.sql</span><br><span class="line"><span class="comment"># 添加如下内容</span></span><br><span class="line">create table test.test(</span><br><span class="line">name string,</span><br><span class="line">friends array&lt;string&gt;,</span><br><span class="line">children map&lt;string, int&gt;,</span><br><span class="line">address struct&lt;street:string, city:string&gt;</span><br><span class="line">)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;,&#x27;</span></span><br><span class="line">collection items terminated by <span class="string">&#x27;_&#x27;</span></span><br><span class="line">map keys terminated by <span class="string">&#x27;:&#x27;</span></span><br><span class="line">lines terminated by <span class="string">&#x27;\n&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>上传数据文件test.txt</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hdfs dfs -put test.txt /user/hive/warehouse/test.db/<span class="built_in">test</span></span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>测试查询</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive</span><br><span class="line">use <span class="built_in">test</span>;</span><br><span class="line">select name,friends[1],children[<span class="string">&quot;xiao song&quot;</span>],address.city from <span class="built_in">test</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="4-3-类型转化"><a href="#4-3-类型转化" class="headerlink" title="4.3 类型转化"></a>4.3 类型转化</h2><p>Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。</p>
<ul>
<li>  隐式类型转换规则如下</li>
</ul>
<ol>
<li> 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。</li>
<li> 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。</li>
<li> TINYINT、SMALLINT、INT都可以转换为FLOAT。</li>
<li> BOOLEAN类型不可以转换为任何其它的类型。</li>
</ol>
<ul>
<li>可以使用CAST操作显示进行数据类型转换  例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select <span class="string">&#x27;1&#x27;</span>+2, cast(<span class="string">&#x27;1&#x27;</span>as int) + 2;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="五、DDL数据库定义语言"><a href="#五、DDL数据库定义语言" class="headerlink" title="五、DDL数据库定义语言"></a>五、DDL数据库定义语言</h1><h2 id="5-1-创建数据库"><a href="#5-1-创建数据库" class="headerlink" title="5.1 创建数据库"></a>5.1 创建数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">CREATE DATABASE [IF NOT EXISTS] database_name</span><br><span class="line">[COMMENT database_comment]</span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[WITH DBPROPERTIES (property_name=property_value, ...)];</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  实例：新建一个名为test1的数据库，存储在HDFS中的 /test 路径下</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create database test1 comment <span class="string">&quot;test1 database&quot;</span> location <span class="string">&quot;/test&quot;</span> with dbproperties(<span class="string">&quot;zhangsan&quot;</span>=<span class="string">&quot;lisi&quot;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-2-显示所有数据库"><a href="#5-2-显示所有数据库" class="headerlink" title="5.2 显示所有数据库"></a>5.2 显示所有数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">show databases;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-3-过滤显示数据库"><a href="#5-3-过滤显示数据库" class="headerlink" title="5.3 过滤显示数据库"></a>5.3 过滤显示数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">show databases like <span class="string">&#x27;test&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-4-显示指定数据库的信息"><a href="#5-4-显示指定数据库的信息" class="headerlink" title="5.4 显示指定数据库的信息"></a>5.4 显示指定数据库的信息</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc database test1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-5-显示指定数据库的详细信息"><a href="#5-5-显示指定数据库的详细信息" class="headerlink" title="5.5 显示指定数据库的详细信息"></a>5.5 显示指定数据库的详细信息</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc database extended test1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-6-切换数据库"><a href="#5-6-切换数据库" class="headerlink" title="5.6 切换数据库"></a>5.6 切换数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">use test1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-7-修改数据库"><a href="#5-7-修改数据库" class="headerlink" title="5.7 修改数据库"></a>5.7 修改数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter database test1 <span class="built_in">set</span> dbproperties(<span class="string">&#x27;name&#x27;</span>=<span class="string">&#x27;zhangsan&#x27;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-8-删除空的数据库"><a href="#5-8-删除空的数据库" class="headerlink" title="5.8 删除空的数据库"></a>5.8 删除空的数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">drop database test1;</span><br><span class="line">drop database <span class="keyword">if</span> exists test1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-9-删除非空数据库"><a href="#5-9-删除非空数据库" class="headerlink" title="5.9 删除非空数据库"></a>5.9 删除非空数据库</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">drop database test1 cascade;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-10-创建数据表"><a href="#5-10-创建数据表" class="headerlink" title="5.10 创建数据表"></a>5.10 创建数据表</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name</span><br><span class="line">[(col_name data_type [COMMENT col_comment], ...)]</span><br><span class="line">[COMMENT table_comment]</span><br><span class="line">[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] <span class="comment">## 分区表在HDFS中体现为分为多个文件夹</span></span><br><span class="line">[CLUSTERED BY (col_name, col_name, ...) <span class="comment">## 分区表在HDFS中体现为一个文件夹下分为多个文件</span></span><br><span class="line">[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]</span><br><span class="line">[ROW FORMAT row_format]</span><br><span class="line">[STORED AS file_format]</span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[TBLPROPERTIES (property_name=property_value, ...)]</span><br><span class="line">[AS select_statement]</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  实例：新建一个名为student1的表，存储在HDFS中的 /student 路径下</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table student1(id int comment <span class="string">&quot;Identity&quot;</span>, age int comment <span class="string">&quot;Age&quot;</span>) comment <span class="string">&quot;Student&quot;</span> row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span> location <span class="string">&#x27;/student&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-11-显示指定表的信息和创建表时的配置"><a href="#5-11-显示指定表的信息和创建表时的配置" class="headerlink" title="5.11 显示指定表的信息和创建表时的配置"></a>5.11 显示指定表的信息和创建表时的配置</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc student1;</span><br><span class="line">show create table student1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-12-显示指定表的详细信息"><a href="#5-12-显示指定表的详细信息" class="headerlink" title="5.12 显示指定表的详细信息"></a>5.12 显示指定表的详细信息</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc formatted student1;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-13-外部表和管理表"><a href="#5-13-外部表和管理表" class="headerlink" title="5.13 外部表和管理表"></a>5.13 外部表和管理表</h2><ul>
<li>  管理表（managed_table）</li>
</ul>
<p>删除表后，存储在HDFS上的数据也会被删除</p>
<ul>
<li>  外部表（external_table）</li>
</ul>
<p>删除表后，存储在HDFS上的数据不会被删除</p>
<h2 id="5-14-将数据表修改为外部表"><a href="#5-14-将数据表修改为外部表" class="headerlink" title="5.14 将数据表修改为外部表"></a>5.14 将数据表修改为外部表</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table student1 <span class="built_in">set</span> tblproperties(<span class="string">&#x27;EXTERNAL&#x27;</span>=<span class="string">&#x27;TRUE&#x27;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-15-将数据表修改为管理表"><a href="#5-15-将数据表修改为管理表" class="headerlink" title="5.15 将数据表修改为管理表"></a>5.15 将数据表修改为管理表</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table student2 <span class="built_in">set</span> tblproperties(<span class="string">&#x27;EXTERNAL&#x27;</span>=<span class="string">&#x27;FALSE&#x27;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-16-创建分区表"><a href="#5-16-创建分区表" class="headerlink" title="5.16 创建分区表"></a>5.16 创建分区表</h2><p>谓词下退，先走过滤<br><strong>1. 新建表</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table dept_partition(deptno int, dname string, loc string)</span><br><span class="line">partitioned by (month string)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>2. 加载数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; load data <span class="built_in">local</span> inpath <span class="string">&#x27;/opt/module/datas/dept.txt&#x27;</span> into table default.dept_partition partition(month=<span class="string">&#x27;201709&#x27;</span>);</span><br><span class="line">hive (default)&gt; load data <span class="built_in">local</span> inpath <span class="string">&#x27;/opt/module/datas/dept.txt&#x27;</span> into table default.dept_partition partition(month=<span class="string">&#x27;201708&#x27;</span>);</span><br><span class="line">hive (default)&gt; load data <span class="built_in">local</span> inpath <span class="string">&#x27;/opt/module/datas/dept.txt&#x27;</span> into table default.dept_partition partition(month=<span class="string">&#x27;201707&#x27;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>3. 查询表数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select * from dept_partition <span class="built_in">where</span> month=<span class="string">&#x27;201709&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>4. 联合查询表数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select * from dept_partition <span class="built_in">where</span> month=<span class="string">&#x27;201709&#x27;</span> union</span><br><span class="line">select * from dept_partition <span class="built_in">where</span> month=<span class="string">&#x27;201708&#x27;</span> union</span><br><span class="line">select * from dept_partition <span class="built_in">where</span> month=<span class="string">&#x27;201707&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-17-新增分区"><a href="#5-17-新增分区" class="headerlink" title="5.17 新增分区"></a>5.17 新增分区</h2><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; alter table dept_partition add partition(month=&#x27;201705&#x27;) partition(month=&#x27;201704&#x27;);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-18-删除分区"><a href="#5-18-删除分区" class="headerlink" title="5.18 删除分区"></a>5.18 删除分区</h2><figure class="highlight apache"><table><tr><td class="code"><pre><span class="line"><span class="attribute">hive</span> (default)&gt; alter table dept_partition drop partition(month=&#x27;<span class="number">201705</span>&#x27;), partition(month=&#x27;<span class="number">201704</span>&#x27;);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-19-查询分区表有多少分区"><a href="#5-19-查询分区表有多少分区" class="headerlink" title="5.19 查询分区表有多少分区"></a>5.19 查询分区表有多少分区</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">show partitions dept_partition;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-20-查询分区表的结构"><a href="#5-20-查询分区表的结构" class="headerlink" title="5.20 查询分区表的结构"></a>5.20 查询分区表的结构</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc formatted dept_partition;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-21-创建二级分区表"><a href="#5-21-创建二级分区表" class="headerlink" title="5.21 创建二级分区表"></a>5.21 创建二级分区表</h2><ol>
<li> 新建表</li>
</ol>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table dept_partition2(deptno int, dname string, loc string)</span><br><span class="line">partitioned by (month string, day string)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ol start="2">
<li> 加载数据</li>
</ol>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">load data <span class="built_in">local</span> inpath <span class="string">&#x27;/opt/module/datas/dept.txt&#x27;</span> into table</span><br><span class="line"> default.dept_partition2 partition(month=<span class="string">&#x27;201709&#x27;</span>, day=<span class="string">&#x27;13&#x27;</span>);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-22-修复分区"><a href="#5-22-修复分区" class="headerlink" title="5.22 修复分区"></a>5.22 修复分区</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">msck repair table dept_partition2;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-23-重命名表"><a href="#5-23-重命名表" class="headerlink" title="5.23 重命名表"></a>5.23 重命名表</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table dept_partition2 rename to dept_partition3;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-24-添加列"><a href="#5-24-添加列" class="headerlink" title="5.24 添加列"></a>5.24 添加列</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table dept_partition add columns(deptdesc string);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-25-更新列"><a href="#5-25-更新列" class="headerlink" title="5.25 更新列"></a>5.25 更新列</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table dept_partition change column deptdesc desc int;</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="5-26-替换列"><a href="#5-26-替换列" class="headerlink" title="5.26 替换列"></a>5.26 替换列</h2><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">alter table dept_partition replace columns(deptno string, dname  </span><br><span class="line"> string, loc string);</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="六、DML数据库操作语言"><a href="#六、DML数据库操作语言" class="headerlink" title="六、DML数据库操作语言"></a>六、DML数据库操作语言</h1><h2 id="6-1-数据导入"><a href="#6-1-数据导入" class="headerlink" title="6.1 数据导入"></a>6.1 数据导入</h2><h3 id="6-1-1-向表中装载数据"><a href="#6-1-1-向表中装载数据" class="headerlink" title="6.1.1 向表中装载数据"></a>6.1.1 向表中装载数据</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">load data [<span class="built_in">local</span>] inpath <span class="string">&#x27;/opt/module/datas/student.txt&#x27;</span> [overwrite] into table student [partition (partcol1=val1,…)];</span><br><span class="line"><span class="comment"># local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表</span></span><br><span class="line"><span class="comment"># overwrite:表示覆盖表中已有数据，否则表示追加</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-1-2-通过查询语句向表中插入数据"><a href="#6-1-2-通过查询语句向表中插入数据" class="headerlink" title="6.1.2 通过查询语句向表中插入数据"></a>6.1.2 通过查询语句向表中插入数据</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 基本插入</span></span><br><span class="line">insert into table student values(1,<span class="string">&#x27;wangwu&#x27;</span>),(2,<span class="string">&#x27;zhaoliu&#x27;</span>);</span><br><span class="line"><span class="comment"># 查询插入</span></span><br><span class="line">insert overwrite table student</span><br><span class="line">select id, name from student <span class="built_in">where</span> id&gt;10;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-1-3-查询语句中创建表并加载数据（As-Select）"><a href="#6-1-3-查询语句中创建表并加载数据（As-Select）" class="headerlink" title="6.1.3 查询语句中创建表并加载数据（As Select）"></a>6.1.3 查询语句中创建表并加载数据（As Select）</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table <span class="keyword">if</span> not exists student3 as select id, name from student;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-1-4-创建表时通过Location指定加载数据路径"><a href="#6-1-4-创建表时通过Location指定加载数据路径" class="headerlink" title="6.1.4 创建表时通过Location指定加载数据路径"></a>6.1.4 创建表时通过Location指定加载数据路径</h3><p>上传数据至HDFS</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; dfs -mkdir /student;</span><br><span class="line">hive (default)&gt; dfs -put /opt/module/datas/student.txt /student;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>创建表，并指定HDFS上的位置</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create external table <span class="keyword">if</span> not exists student5(id int, name string)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">location <span class="string">&#x27;/student;</span></span><br><span class="line"><span class="string"></span></span><br></pre></td></tr></table></figure>

<p>查询数据</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; select * from student5;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-1-5-Export导出到HDFS上"><a href="#6-1-5-Export导出到HDFS上" class="headerlink" title="6.1.5 Export导出到HDFS上"></a>6.1.5 Export导出到HDFS上</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 先用export导出后，再将数据导入</span></span><br><span class="line">import table student2 from <span class="string">&#x27;/user/hive/warehouse/export/student&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="6-2-数据导出"><a href="#6-2-数据导出" class="headerlink" title="6.2 数据导出"></a>6.2 数据导出</h2><h3 id="6-2-1-Insert导出"><a href="#6-2-1-Insert导出" class="headerlink" title="6.2.1 Insert导出"></a>6.2.1 Insert导出</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 导出到本地</span></span><br><span class="line">insert overwrite <span class="built_in">local</span> directory <span class="string">&#x27;/opt/module/datas/export/student1&#x27;</span></span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">select * from student;</span><br><span class="line"><span class="comment"># 导出到HDFS</span></span><br><span class="line">insert overwrite directory <span class="string">&#x27;/user/lytdev/student2&#x27;</span></span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">select * from student;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-2-2-Hadoop命令导出到本地"><a href="#6-2-2-Hadoop命令导出到本地" class="headerlink" title="6.2.2 Hadoop命令导出到本地"></a>6.2.2 Hadoop命令导出到本地</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; dfs -get /user/hive/warehouse/student/month=201709/000000_0</span><br><span class="line">/opt/module/datas/<span class="built_in">export</span>/student3.txt;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-2-3-Hive-Shell-命令导出"><a href="#6-2-3-Hive-Shell-命令导出" class="headerlink" title="6.2.3 Hive Shell 命令导出"></a>6.2.3 Hive Shell 命令导出</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">bin/hive -e <span class="string">&#x27;select * from default.student;&#x27;</span> &gt; /opt/module/datas/<span class="built_in">export</span>/student4.txt;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="6-2-4-Export导出到HDFS上"><a href="#6-2-4-Export导出到HDFS上" class="headerlink" title="6.2.4 Export导出到HDFS上"></a>6.2.4 Export导出到HDFS上</h3><p>export和import主要用于两个Hadoop平台集群之间Hive表迁移</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 既能导出元数据，也能导出数据</span></span><br><span class="line"><span class="built_in">export</span> table default.student to <span class="string">&#x27;/user/hive/warehouse/export/student&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="6-3-清除数据（不清除元数据）"><a href="#6-3-清除数据（不清除元数据）" class="headerlink" title="6.3 清除数据（不清除元数据）"></a>6.3 清除数据（不清除元数据）</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># Truncate只能删除管理表，不能删除外部表中数据</span></span><br><span class="line">truncate table student;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="七、基本查询"><a href="#七、基本查询" class="headerlink" title="七、基本查询"></a>七、基本查询</h1><p><strong>（1）写SQL语句关键字的顺序</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line"><span class="keyword">from</span></span><br><span class="line"><span class="keyword">join</span> <span class="keyword">on</span></span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line"><span class="keyword">having</span></span><br><span class="line">limit</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（1）执行SQL语句关键字的顺序</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">from</span></span><br><span class="line"><span class="keyword">join</span></span><br><span class="line"><span class="keyword">on</span></span><br><span class="line"><span class="keyword">where</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span>(开始使用<span class="keyword">select</span>中的别名，后面的语句中都可以使用)</span><br><span class="line">avg,sum....</span><br><span class="line"><span class="keyword">having</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line"><span class="keyword">distinct</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line">limit</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（1）谓词下推</strong></p>
<p>先走过滤，再走查询<br><strong>（1）SQL优化</strong></p>
<p>在join on条件中，可以使用子查询语句仅需优化</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">id,name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">A <span class="keyword">join</span> B</span><br><span class="line"><span class="keyword">on</span> A.id<span class="operator">=</span>B.id <span class="keyword">and</span> A.id<span class="operator">&gt;</span><span class="number">100</span>;</span><br><span class="line">优化后<span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span><span class="operator">=</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">id,name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span> id,name <span class="keyword">from</span> A <span class="keyword">where</span> A.id<span class="operator">&gt;</span><span class="number">100</span>) t1</span><br><span class="line"><span class="keyword">join</span> B</span><br><span class="line"><span class="keyword">on</span> t1.id<span class="operator">=</span>B.id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（2） 数据准备</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 创建部门表</span></span><br><span class="line">create table <span class="keyword">if</span> not exists dept(deptno int, dname string, loc int)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 创建员工表</span></span><br><span class="line">create table <span class="keyword">if</span> not exists emp(empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int)</span><br><span class="line">row format delimited fields terminated by <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 向部门表导入数据</span></span><br><span class="line">load data <span class="built_in">local</span> inpath <span class="string">&#x27;/home/lytdev/dept.txt&#x27;</span> into table dept;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 向员工表导入数据</span></span><br><span class="line">load data <span class="built_in">local</span> inpath <span class="string">&#x27;/home/lytdev/emp.txt&#x27;</span> into table emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（3） 全表和特定列查询</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 全表查询</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp;</span><br><span class="line"># 特定列查询</span><br><span class="line"><span class="keyword">select</span> empno, ename <span class="keyword">from</span> emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（4） 列别名</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> ename <span class="keyword">as</span> name, deptno dn <span class="keyword">from</span> emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（5）算术运算符</strong><br>运算符 描述<br>A + B A和B相加<br>A - B A和B相减<br>A * B A和B相乘<br>A / B A和B相除<br>A % B A对B取余<br>A &amp; B A和B按位取余<br>A｜B A和B按位取或<br>A ^ B A和B按位取异或<br>~A A按位取反</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> sal <span class="operator">+</span><span class="number">1</span> <span class="keyword">from</span> emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（6）常用函数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 求总行数</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) cnt <span class="keyword">from</span> emp;</span><br><span class="line"># 求最大值</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal) max_sal <span class="keyword">from</span> emp;</span><br><span class="line"># 求最小值</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">min</span>(sal) min_sal <span class="keyword">from</span> emp;</span><br><span class="line"># 求总和</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">sum</span>(sal) sum_sal <span class="keyword">from</span> emp;</span><br><span class="line"># 求平均值</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(sal) avg_sal <span class="keyword">from</span> emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（7） Limit语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp limit <span class="number">5</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（8）where语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># <span class="keyword">where</span>子句中不能使用字段别名</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">&gt;</span><span class="number">1000</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（9）比较运算符</strong><br>操作符 支持的数据类型 描述<br>A = B 基本数据类型 如果A等于B则返回TRUE，反之返回FALSE<br>A &lt;=&gt; B 基本数据类型 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL<br>A&lt;&gt;B, A!=B 基本数据类型 A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE<br>A&lt;B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE<br>A&lt;=B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE<br>A&gt;B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE<br>A&gt;=B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE<br>A [NOT] BETWEEN B AND C 基本数据类型 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。<br>A IS NULL 所有数据类型 如果A等于NULL，则返回TRUE，反之返回FALSE<br>A IS NOT NULL 所有数据类型 如果A不等于NULL，则返回TRUE，反之返回FALSE<br>IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值<br>A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。<br>A RLIKE B, A REGEXP B STRING 类型 B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 查询出薪水等于<span class="number">5000</span>的所有员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">=</span> <span class="number">5000</span>;</span><br><span class="line"># 查询工资在<span class="number">500</span>到<span class="number">1000</span>的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">between</span> <span class="number">500</span> <span class="keyword">and</span> <span class="number">1000</span>;</span><br><span class="line"># 查询comm为空的所有员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> comm <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"># 查询工资是<span class="number">1500</span>或<span class="number">5000</span>的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">IN</span> (<span class="number">1500</span>, <span class="number">5000</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（10）like和rlike</strong></p>
<p>**rlike：**带有正则表达式的like语句<br>正则匹配 描述<br>\ 转义<br>^ 一行的开头<br>^R 匹配以R为开头的行<br>$ 匹配一行的结尾<br>R$ 匹配以R为结尾的行</p>
<ul>
<li>表示上一个子式匹配0次或多次，贪心匹配<br>  Zo* Zo Zoo Zooo<br>  . 匹配一个任意的字符<br>  .* 匹配任意字符串<br>  [] 匹配某个范围内的字符<br>  [a-z] 匹配一个a-z之间的字符<br>  [a-z]* 匹配任意字母字符串</li>
</ul>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 查找以<span class="number">2</span>开头薪水的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">LIKE</span> <span class="string">&#x27;2%&#x27;</span>;</span><br><span class="line"># 查找第二个数值为<span class="number">2</span>的薪水的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">LIKE</span> <span class="string">&#x27;_2%&#x27;</span>;</span><br><span class="line"># 查找薪水中含有<span class="number">2</span>的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal RLIKE <span class="string">&#x27;[2]&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（11）逻辑运算符</strong><br>操作符 描述<br>and 逻辑并<br>or 逻辑或<br>not 逻辑否</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 查询薪水大于<span class="number">1000</span>，部门是<span class="number">30</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal<span class="operator">&gt;</span><span class="number">1000</span> <span class="keyword">and</span> deptno<span class="operator">=</span><span class="number">30</span>;</span><br><span class="line"># 查询薪水大于<span class="number">1000</span>，或者部门是<span class="number">30</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal<span class="operator">&gt;</span><span class="number">1000</span> <span class="keyword">or</span> deptno<span class="operator">=</span><span class="number">30</span>;</span><br><span class="line"># 查询除了<span class="number">20</span>部门和<span class="number">30</span>部门以外的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> deptno <span class="keyword">not</span> <span class="keyword">IN</span>(<span class="number">30</span>, <span class="number">20</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="八、分组"><a href="#八、分组" class="headerlink" title="八、分组"></a>八、分组</h1><h2 id="8-1-group-by语句"><a href="#8-1-group-by语句" class="headerlink" title="8.1 group by语句"></a>8.1 group by语句</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 计算emp表每个部门的平均工资</span><br><span class="line"><span class="keyword">select</span> t.deptno, <span class="built_in">avg</span>(t.sal) avg_sal <span class="keyword">from</span> emp t <span class="keyword">group</span> <span class="keyword">by</span> t.deptno;</span><br><span class="line"># 计算emp每个部门中每个岗位的最高薪水</span><br><span class="line"><span class="keyword">select</span> t.deptno, t.job, <span class="built_in">max</span>(t.sal) max_sal <span class="keyword">from</span> emp t <span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">t.deptno, t.job;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="8-2-having语句"><a href="#8-2-having语句" class="headerlink" title="8.2 having语句"></a>8.2 having语句</h2><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">having和where不同点</span><br><span class="line"></span><br><span class="line">where后面不能写分组函数，而having后面可以使用分组函数  </span><br><span class="line">having只用于group by分组统计语句</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 求每个部门的平均工资</span><br><span class="line"><span class="keyword">select</span> deptno, <span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line"># 求每个部门的平均薪水大于<span class="number">2000</span>的部门</span><br><span class="line"><span class="keyword">select</span> deptno, <span class="built_in">avg</span>(sal) avg_sal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">having</span></span><br><span class="line">avg_sal <span class="operator">&gt;</span> <span class="number">2000</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="九、join语句"><a href="#九、join语句" class="headerlink" title="九、join语句"></a>九、join语句</h1><h2 id="9-1-等值join"><a href="#9-1-等值join" class="headerlink" title="9.1 等值join"></a>9.1 等值join</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno, d.dname <span class="keyword">from</span> emp e <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-2-表的别名"><a href="#9-2-表的别名" class="headerlink" title="9.2 表的别名"></a>9.2 表的别名</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 合并员工表和部门表</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno <span class="keyword">from</span> emp e <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-3-内连接"><a href="#9-3-内连接" class="headerlink" title="9.3 内连接"></a>9.3 内连接</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno <span class="keyword">from</span> emp e <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-4-左外连接"><a href="#9-4-左外连接" class="headerlink" title="9.4 左外连接"></a>9.4 左外连接</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># <span class="keyword">JOIN</span>操作符左边表中符合<span class="keyword">WHERE</span>子句的所有记录将会被返回</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno <span class="keyword">from</span> emp e <span class="keyword">left</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-5-右外连接"><a href="#9-5-右外连接" class="headerlink" title="9.5 右外连接"></a>9.5 右外连接</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># <span class="keyword">JOIN</span>操作符右边表中符合<span class="keyword">WHERE</span>子句的所有记录将会被返回</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno <span class="keyword">from</span> emp e <span class="keyword">right</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-6-满外连接"><a href="#9-6-满外连接" class="headerlink" title="9.6 满外连接"></a>9.6 满外连接</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 将会返回所有表中符合<span class="keyword">WHERE</span>语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用<span class="keyword">NULL</span>值替代</span><br><span class="line"><span class="keyword">select</span> e.empno, e.ename, d.deptno <span class="keyword">from</span> emp e <span class="keyword">full</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-7-多表连接"><a href="#9-7-多表连接" class="headerlink" title="9.7 多表连接"></a>9.7 多表连接</h2><p>**创建位置表 **</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> if <span class="keyword">not</span> <span class="keyword">exists</span> location(loc <span class="type">int</span>, loc_name string)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/home/lytdev/location.txt&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> location;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>多表连接查询</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> e.ename, d.dname, l.loc_name</span><br><span class="line"><span class="keyword">FROM</span> emp e</span><br><span class="line"><span class="keyword">JOIN</span> dept d</span><br><span class="line"><span class="keyword">ON</span> d.deptno <span class="operator">=</span> e.deptno</span><br><span class="line"><span class="keyword">JOIN</span> location l</span><br><span class="line"><span class="keyword">ON</span> d.loc <span class="operator">=</span> l.loc;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-8-笛卡尔积"><a href="#9-8-笛卡尔积" class="headerlink" title="9.8 笛卡尔积"></a>9.8 笛卡尔积</h2><p>hive中严禁使用笛卡尔积</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">产生笛卡尔的条件</span><br><span class="line"></span><br><span class="line">省略连接条件  </span><br><span class="line">连接条件无效  </span><br><span class="line">所有表中的所有行互相连接</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="9-9-连接谓词中不支持or"><a href="#9-9-连接谓词中不支持or" class="headerlink" title="9.9 连接谓词中不支持or"></a>9.9 连接谓词中不支持or</h2><p>hive join目前不支持在on子句中使用谓词or</p>
<h1 id="十、排序"><a href="#十、排序" class="headerlink" title="十、排序"></a>十、排序</h1><h2 id="10-1-全局排序"><a href="#10-1-全局排序" class="headerlink" title="10.1 全局排序"></a>10.1 全局排序</h2><p>排序规则</p>
<p>asc： 升序<br>desc：降序</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 查询员工信息按工资升序排列</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal;</span><br><span class="line"># 查询员工信息按工资降序排列</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="10-2-按照别名排序"><a href="#10-2-按照别名排序" class="headerlink" title="10.2 按照别名排序"></a>10.2 按照别名排序</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 按照员工薪水的<span class="number">2</span>倍排序</span><br><span class="line"><span class="keyword">select</span> ename, sal<span class="operator">*</span><span class="number">2</span> twosal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> twosal;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="10-3-多个列排序"><a href="#10-3-多个列排序" class="headerlink" title="10.3 多个列排序"></a>10.3 多个列排序</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 按照部门和工资升序排序</span><br><span class="line"><span class="keyword">select</span> ename, deptno, sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> deptno, sal;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="10-4-每个MapReduce内部排序"><a href="#10-4-每个MapReduce内部排序" class="headerlink" title="10.4 每个MapReduce内部排序"></a>10.4 每个MapReduce内部排序</h2><p>Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by，按照分区排序。</p>
<p>Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 设置reduce个数</span><br><span class="line"><span class="keyword">set</span> mapreduce.job.reduces<span class="operator">=</span><span class="number">3</span>;</span><br><span class="line"># 查看设置reduce个数</span><br><span class="line"><span class="keyword">set</span> mapreduce.job.reduces;</span><br><span class="line"># 根据部门编号降序查看员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp sort <span class="keyword">by</span> deptno <span class="keyword">desc</span>;</span><br><span class="line"># 将查询结果导入到文件中（按照部门编号降序排序）</span><br><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">local</span> directory <span class="string">&#x27;/home/lytdev/datas/sortby-result&#x27;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp sort <span class="keyword">by</span> deptno <span class="keyword">desc</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="10-5-分区排序"><a href="#10-5-分区排序" class="headerlink" title="10.5 分区排序"></a>10.5 分区排序</h2><p>Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。</p>
<p>对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 设置reduce个数</span><br><span class="line"><span class="keyword">set</span> mapreduce.job.reduces<span class="operator">=</span><span class="number">3</span>;</span><br><span class="line"># 先按照部门编号分区，再按照员工编号降序排序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp distribute <span class="keyword">by</span> deptno sort <span class="keyword">by</span> empno <span class="keyword">desc</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>排序规则</p>
<p>distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区<br>Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前</p>
<h2 id="10-6-Cluster-By"><a href="#10-6-Cluster-By" class="headerlink" title="10.6 Cluster By"></a>10.6 Cluster By</h2><p>当distribute by和sorts by字段相同时，可以使用cluster by方式</p>
<p>cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 按照部门编号分区排序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp cluster <span class="keyword">by</span> deptno;</span><br><span class="line"># 与上面语句等价</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp distribute <span class="keyword">by</span> deptno sort <span class="keyword">by</span> deptno;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="十一、分桶及抽样查询"><a href="#十一、分桶及抽样查询" class="headerlink" title="十一、分桶及抽样查询"></a>十一、分桶及抽样查询</h1><h2 id="11-1-分桶表数据存储"><a href="#11-1-分桶表数据存储" class="headerlink" title="11.1 分桶表数据存储"></a>11.1 分桶表数据存储</h2><p><strong>创建分桶表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu_buck(id <span class="type">int</span>, name string)</span><br><span class="line">clustered <span class="keyword">by</span>(id)</span><br><span class="line"><span class="keyword">into</span> <span class="number">4</span> buckets</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建临时表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(id <span class="type">int</span>, name string)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据至临时表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/home/lytdev/student.txt&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> stu;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>设置强制分桶</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">set</span> hive.enforce.bucketing<span class="operator">=</span><span class="literal">true</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>设置reduce个数</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 让hive自己去决定分桶个数</span></span><br><span class="line"><span class="built_in">set</span> mapreduce.job.reduces=-1;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据至分桶表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> stu_buck <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> stu;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="11-2-分桶抽样查询"><a href="#11-2-分桶抽样查询" class="headerlink" title="11.2 分桶抽样查询"></a>11.2 分桶抽样查询</h2><p>tablesample((bucket x out of y)</p>
<p>y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 按照id抽样查询，将数据分<span class="number">4</span>份，每一份取第<span class="number">1</span>个数据</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> stu_buck <span class="keyword">tablesample</span>(bucket <span class="number">1</span> <span class="keyword">out</span> <span class="keyword">of</span> <span class="number">4</span> <span class="keyword">on</span> id);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="十二、其他常用查询函数"><a href="#十二、其他常用查询函数" class="headerlink" title="十二、其他常用查询函数"></a>十二、其他常用查询函数</h1><h2 id="12-1-空字段赋值"><a href="#12-1-空字段赋值" class="headerlink" title="12.1 空字段赋值"></a>12.1 空字段赋值</h2><blockquote>
<p>函数说明<br>NVL：给值为NULL的数据赋值，它的格式是NVL( value，default\_value)。它的功能是如果value为NULL，则NVL函数返回default\_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 如果员工的comm为<span class="keyword">NULL</span>，则用<span class="number">-1</span>代替</span><br><span class="line"><span class="keyword">select</span> comm,nvl(comm, <span class="number">-1</span>) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line"># 如果员工的comm为<span class="keyword">NULL</span>，则用领导id代替</span><br><span class="line"><span class="keyword">select</span> comm, nvl(comm,mgr) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-2-时间类"><a href="#12-2-时间类" class="headerlink" title="12.2 时间类"></a>12.2 时间类</h2><p><strong>date_format格式化时间</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> date_format(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="string">&#x27;yyyy-MM-dd&#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>date_add时间相加天数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> date_add(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="number">5</span>);</span><br><span class="line"><span class="keyword">select</span> date_add(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="number">-5</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>date_sub时间相减天数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> date_sub(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="number">5</span>);</span><br><span class="line"><span class="keyword">select</span> date_sub(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="number">-5</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>两个时间相减得天数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> datediff(<span class="string">&#x27;2019-06-12&#x27;</span>, <span class="string">&#x27;2019-06-10&#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>替换函数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> regexp_replace(<span class="string">&#x27;2019/06/12&#x27;</span>, <span class="string">&#x27;/&#x27;</span>, <span class="string">&#x27;-&#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-3-CASE-WHEN"><a href="#12-3-CASE-WHEN" class="headerlink" title="12.3 CASE WHEN"></a>12.3 CASE WHEN</h2><p><strong>数据准备</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">姓名 部门 性别</span><br><span class="line">悟空 A 男</span><br><span class="line">大海 A 男</span><br><span class="line">宋宋 B 男</span><br><span class="line">凤姐 A 女</span><br><span class="line">婷姐 B 女</span><br><span class="line">婷婷 B 女</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> emp_sex(name string, dept_id string, sex string)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> &quot;\t&quot;;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<p>load data local inpath ‘/home/lytdev/emp_sex.txt’ into table emp_sex;</p>
<p><strong>查询语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># 求出不同部门男女各多少人。结果如下：</span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">dept_id,</span><br><span class="line"><span class="built_in">sum</span>(<span class="keyword">case</span> sex <span class="keyword">when</span> <span class="string">&#x27;男&#x27;</span> <span class="keyword">then</span> <span class="number">1</span> <span class="keyword">else</span> <span class="number">0</span> <span class="keyword">end</span>) male_count,</span><br><span class="line"><span class="built_in">sum</span>(<span class="keyword">case</span> sex <span class="keyword">when</span> <span class="string">&#x27;女&#x27;</span> <span class="keyword">then</span> <span class="number">1</span> <span class="keyword">else</span> <span class="number">0</span> <span class="keyword">end</span>) female_count</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">emp_sex</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">dept_id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-4-行转列"><a href="#12-4-行转列" class="headerlink" title="12.4 行转列"></a>12.4 行转列</h2><p><strong>数据准备</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">姓名 星座 血型</span><br><span class="line">孙悟空 白羊座 A</span><br><span class="line">大海 射手座 A</span><br><span class="line">宋宋 白羊座 B</span><br><span class="line">猪八戒 白羊座 A</span><br><span class="line">凤姐 射手座 A</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>**需求  **<br>把星座和血型一样的人归类到一起。结果如下：</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">射手座,A 大海|凤姐</span><br><span class="line">白羊座,A 孙悟空|猪八戒</span><br><span class="line">白羊座,B 宋宋|苍老师</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建本地constellation.txt</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi constellation.txt</span><br><span class="line">孙悟空 白羊座 A</span><br><span class="line">大海 射手座 A</span><br><span class="line">宋宋 白羊座 B</span><br><span class="line">猪八戒 白羊座 A</span><br><span class="line">凤姐 射手座 A</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建hive表</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table person_info(name string, constellation string, blood_type string)</span><br><span class="line">row format delimited fields terminated by <span class="string">&quot;\t&quot;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">load data <span class="built_in">local</span> inpath <span class="string">&quot;/home/lytdev/constellation.txt&quot;</span> into table person_info;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查询语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">t1.base,</span><br><span class="line">concat_ws(<span class="string">&#x27;|&#x27;</span>, collect_set(t1.name)) name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">(<span class="keyword">select</span></span><br><span class="line">name,</span><br><span class="line">concat(constellation, &quot;,&quot;, blood_type) base</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">person_info) t1</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">t1.base;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-5-列转行"><a href="#12-5-列转行" class="headerlink" title="12.5 列转行"></a>12.5 列转行</h2><p><strong>数据准备</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">电源 分类</span><br><span class="line">《疑犯追踪》 悬疑,动作,科幻,剧情</span><br><span class="line">《Lie to me》 悬疑,警匪,动作,心理,剧情</span><br><span class="line">《战狼2》 战争,动作,灾难</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>**需求  **<br>将电影分类中的数组数据展开。结果如下：</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">《疑犯追踪》 悬疑</span><br><span class="line">《疑犯追踪》 动作</span><br><span class="line">《疑犯追踪》 科幻</span><br><span class="line">《疑犯追踪》 剧情</span><br><span class="line">《Lie to me》 悬疑</span><br><span class="line">《Lie to me》 警匪</span><br><span class="line">《Lie to me》 动作</span><br><span class="line">《Lie to me》 心理</span><br><span class="line">《Lie to me》 剧情</span><br><span class="line">《战狼2》 战争</span><br><span class="line">《战狼2》 动作</span><br><span class="line">《战狼2》 灾难</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建本地movie.txt</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi movie.txt</span><br><span class="line">《疑犯追踪》 悬疑,动作,科幻,剧情</span><br><span class="line">《Lie to me》 悬疑,警匪,动作,心理,剧情</span><br><span class="line">《战狼2》 战争,动作,灾难</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建hive表</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table movie_info(movie string, category array&lt;string&gt;)</span><br><span class="line">row format delimited fields terminated by <span class="string">&quot;\t&quot;</span></span><br><span class="line">collection items terminated by <span class="string">&quot;,&quot;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">load data <span class="keyword">local</span> inpath &quot;/home/lytdev/movie.txt&quot; <span class="keyword">into</span> <span class="keyword">table</span> movie_info;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查询语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">movie,</span><br><span class="line">category_name</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">movie_info <span class="keyword">lateral</span> <span class="keyword">view</span> explode(category) table_tmp <span class="keyword">as</span> category_name;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-6-窗口函数"><a href="#12-6-窗口函数" class="headerlink" title="12.6 窗口函数"></a>12.6 窗口函数</h2><p><strong>数据准备</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">姓名 购买日期 价格</span><br><span class="line">jack <span class="number">2017</span><span class="number">-01</span><span class="number">-01</span> <span class="number">10</span></span><br><span class="line">tony <span class="number">2017</span><span class="number">-01</span><span class="number">-02</span> <span class="number">15</span></span><br><span class="line">jack <span class="number">2017</span><span class="number">-02</span><span class="number">-03</span> <span class="number">23</span></span><br><span class="line">tony <span class="number">2017</span><span class="number">-01</span><span class="number">-04</span> <span class="number">29</span></span><br><span class="line">jack <span class="number">2017</span><span class="number">-01</span><span class="number">-05</span> <span class="number">46</span></span><br><span class="line">jack <span class="number">2017</span><span class="number">-04</span><span class="number">-06</span> <span class="number">42</span></span><br><span class="line">tony <span class="number">2017</span><span class="number">-01</span><span class="number">-07</span> <span class="number">50</span></span><br><span class="line">jack <span class="number">2017</span><span class="number">-01</span><span class="number">-08</span> <span class="number">55</span></span><br><span class="line">mart <span class="number">2017</span><span class="number">-04</span><span class="number">-08</span> <span class="number">62</span></span><br><span class="line">mart <span class="number">2017</span><span class="number">-04</span><span class="number">-09</span> <span class="number">68</span></span><br><span class="line">neil <span class="number">2017</span><span class="number">-05</span><span class="number">-10</span> <span class="number">12</span></span><br><span class="line">mart <span class="number">2017</span><span class="number">-04</span><span class="number">-11</span> <span class="number">75</span></span><br><span class="line">neil <span class="number">2017</span><span class="number">-06</span><span class="number">-12</span> <span class="number">80</span></span><br><span class="line">mart <span class="number">2017</span><span class="number">-04</span><span class="number">-13</span> <span class="number">94</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建本地business.txt</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi business.txt</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建hive表</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table business(name string, orderdate string,cost int)</span><br><span class="line">ROW FORMAT DELIMITED FIELDS TERMINATED BY <span class="string">&#x27;,&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">load data <span class="built_in">local</span> inpath <span class="string">&quot;/home/lytdev/business.txt&quot;</span> into table business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<blockquote>
<p>按需求查询数据</p>
</blockquote>
<p><strong>查询在2017年4月份购买过的顾客及总人数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> name,<span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">over</span> ()</span><br><span class="line"><span class="keyword">from</span> business</span><br><span class="line"><span class="keyword">where</span> <span class="built_in">substring</span>(orderdate,<span class="number">1</span>,<span class="number">7</span>) <span class="operator">=</span> <span class="string">&#x27;2017-04&#x27;</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> name;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查询顾客的购买明细及月购买总额</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select name,orderdate,cost,sum(cost) over(partition by name, month(orderdate)) from business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>上述的场景, 将每个顾客的cost按照日期进行累加</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> name,orderdate,cost,</span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>() <span class="keyword">as</span> sample1,<span class="comment">--所有行相加</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name) <span class="keyword">as</span> sample2,<span class="comment">--按name分组，组内数据相加</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate) <span class="keyword">as</span> sample3,<span class="comment">--按name分组，组内数据累加</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate <span class="keyword">rows</span> <span class="keyword">between</span> UNBOUNDED PRECEDING <span class="keyword">and</span> <span class="keyword">current</span> <span class="type">row</span> ) <span class="keyword">as</span> sample4 ,<span class="comment">--和sample3一样,由起点到当前行的聚合</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate <span class="keyword">rows</span> <span class="keyword">between</span> <span class="number">1</span> PRECEDING <span class="keyword">and</span> <span class="keyword">current</span> <span class="type">row</span>) <span class="keyword">as</span> sample5, <span class="comment">--当前行和前面一行做聚合</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate <span class="keyword">rows</span> <span class="keyword">between</span> <span class="number">1</span> PRECEDING <span class="keyword">AND</span> <span class="number">1</span> FOLLOWING ) <span class="keyword">as</span> sample6,<span class="comment">--当前行和前边一行及后面一行</span></span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate <span class="keyword">rows</span> <span class="keyword">between</span> <span class="keyword">current</span> <span class="type">row</span> <span class="keyword">and</span> UNBOUNDED FOLLOWING ) <span class="keyword">as</span> sample7 <span class="comment">--当前行及后面所有行</span></span><br><span class="line"><span class="keyword">from</span> business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查询每个顾客上次的购买时间</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> name,orderdate,cost,</span><br><span class="line"><span class="built_in">lag</span>(orderdate,<span class="number">1</span>,<span class="string">&#x27;1900-01-01&#x27;</span>) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate ) <span class="keyword">as</span> time1, <span class="built_in">lag</span>(orderdate,<span class="number">2</span>) <span class="keyword">over</span> (<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate) <span class="keyword">as</span> time2</span><br><span class="line"><span class="keyword">from</span> business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查询前20%时间的订单信息</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> (</span><br><span class="line"><span class="keyword">select</span> name,orderdate,cost, <span class="built_in">ntile</span>(<span class="number">5</span>) <span class="keyword">over</span>(<span class="keyword">order</span> <span class="keyword">by</span> orderdate) sorted</span><br><span class="line"><span class="keyword">from</span> business</span><br><span class="line">) t</span><br><span class="line"><span class="keyword">where</span> sorted <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>over函数中的2种组合</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">name,</span><br><span class="line">orderdate,</span><br><span class="line">cost,</span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(distribute <span class="keyword">by</span> name sort <span class="keyword">by</span> orderdate)</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">name,</span><br><span class="line">orderdate,</span><br><span class="line">cost,</span><br><span class="line"><span class="built_in">sum</span>(cost) <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> name <span class="keyword">order</span> <span class="keyword">by</span> orderdate)</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">business;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-7-Rank"><a href="#12-7-Rank" class="headerlink" title="12.7 Rank"></a>12.7 Rank</h2><p><strong>函数说明</strong></p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">RANK() 排序相同时会重复，总数不会变 1 1 3 4  </span><br><span class="line">DENSE_RANK() 排序相同时会重复，总数会减少 1 1 2 3  </span><br><span class="line">ROW_NUMBER() 会根据顺序计算 1 2 3 4</span><br><span class="line"></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>数据准备</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">姓名 科目 成绩</span><br><span class="line">孙悟空 语文 87</span><br><span class="line">孙悟空 数学 95</span><br><span class="line">孙悟空 英语 68</span><br><span class="line">大海 语文 94</span><br><span class="line">大海 数学 56</span><br><span class="line">大海 英语 84</span><br><span class="line">宋宋 语文 64</span><br><span class="line">宋宋 数学 86</span><br><span class="line">宋宋 英语 84</span><br><span class="line">婷婷 语文 65</span><br><span class="line">婷婷 数学 85</span><br><span class="line">婷婷 英语 78</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>需求<br>计算每门学科成绩排名<br><strong>创建本地score.txt</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">vi score.txt</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>创建hive表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> score(</span><br><span class="line">name string,</span><br><span class="line">subject string,</span><br><span class="line">score <span class="type">int</span>)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> &quot;\t&quot;;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>导入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/home/lytdev/score.txt&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> score;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>按需求查询数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">select</span> name,</span><br><span class="line">subject,</span><br><span class="line">score,</span><br><span class="line"><span class="built_in">rank</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> subject <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) rp,</span><br><span class="line"><span class="built_in">dense_rank</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> subject <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) drp,</span><br><span class="line"><span class="built_in">row_number</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> subject <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) rmp</span><br><span class="line"><span class="keyword">from</span> score;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="12-8-系统函数"><a href="#12-8-系统函数" class="headerlink" title="12.8 系统函数"></a>12.8 系统函数</h2><p><strong>查看系统提供的函数</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">show</span> functions;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>显示指定函数的用法</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc <span class="keyword">function</span> upper;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>详细介绍指定函数的具体用法</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">desc</span> <span class="keyword">function</span> extended split;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="十三、自定义函数UDF"><a href="#十三、自定义函数UDF" class="headerlink" title="十三、自定义函数UDF"></a>十三、自定义函数UDF</h1><h2 id="13-1-自定义函数类型"><a href="#13-1-自定义函数类型" class="headerlink" title="13.1 自定义函数类型"></a>13.1 自定义函数类型</h2><ul>
<li>  UDF函数（user-defined function）</li>
</ul>
<p>一进一出的函数</p>
<ul>
<li>  UDAF函数（user-defined aggregation function）</li>
</ul>
<p>多进一出的函数，例如count、max、min</p>
<ul>
<li>  UDF函数（user-defined table-generating functions）</li>
</ul>
<p>一进多出的函数，例如later view explore()炸裂函数</p>
<h2 id="13-2-创建项目导入依赖"><a href="#13-2-创建项目导入依赖" class="headerlink" title="13.2 创建项目导入依赖"></a>13.2 创建项目导入依赖</h2><figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">dependencies</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.apache.hive<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>hive-exec<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">version</span>&gt;</span>1.2.1<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">dependencies</span>&gt;</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="13-3-创建一个类继承与UDF"><a href="#13-3-创建一个类继承与UDF" class="headerlink" title="13.3 创建一个类继承与UDF"></a>13.3 创建一个类继承与UDF</h2><figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">package com.inspur.hive;</span><br><span class="line"></span><br><span class="line">import org.apache.hadoop.hive.ql.exec.UDF;</span><br><span class="line"></span><br><span class="line">public class Lower extends UDF &#123;</span><br><span class="line">	public int evaluate(String line) &#123;</span><br><span class="line">		if (line == null) &#123;</span><br><span class="line">		return 0;</span><br><span class="line">		&#125; else &#123;</span><br><span class="line">		return line.length();</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br><span class="line">	public int evalute(Number line) &#123;  </span><br><span class="line">		if (line == null) &#123;  </span><br><span class="line">			return 0;  </span><br><span class="line">		&#125; else &#123;  </span><br><span class="line">			return line.toString().length();  </span><br><span class="line">		&#125;  </span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">	public int evalute(Boolean line) &#123;  </span><br><span class="line">		if (line == null) &#123;  </span><br><span class="line">			return 0;  </span><br><span class="line">		&#125; else &#123;  </span><br><span class="line">			return line.toString().length();  </span><br><span class="line">		&#125;  </span><br><span class="line">	&#125;  </span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="13-4-打成jar包，并上传集群"><a href="#13-4-打成jar包，并上传集群" class="headerlink" title="13.4 打成jar包，并上传集群"></a>13.4 打成jar包，并上传集群</h2><p><img src="https://img-blog.csdnimg.cn/20200608195229741.png"></p>
<h2 id="13-5-临时上传jar包至hive，退出时失效"><a href="#13-5-临时上传jar包至hive，退出时失效" class="headerlink" title="13.5 临时上传jar包至hive，退出时失效"></a>13.5 临时上传jar包至hive，退出时失效</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">add jar /home/lytdev/1.jar;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="13-6-创建自定义函数"><a href="#13-6-创建自定义函数" class="headerlink" title="13.6 创建自定义函数"></a>13.6 创建自定义函数</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create <span class="keyword">function</span> mylen as <span class="string">&quot;com.inspur.hive.Lower&quot;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="13-7-测试自定义函数"><a href="#13-7-测试自定义函数" class="headerlink" title="13.7 测试自定义函数"></a>13.7 测试自定义函数</h2><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select ename, mylen(ename) from emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h1 id="十四、压缩与存储"><a href="#十四、压缩与存储" class="headerlink" title="十四、压缩与存储"></a>十四、压缩与存储</h1><h2 id="14-1-开启Map输出阶段压缩"><a href="#14-1-开启Map输出阶段压缩" class="headerlink" title="14.1 开启Map输出阶段压缩"></a>14.1 开启Map输出阶段压缩</h2><p>开启hive中间传输数据压缩功能</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt;<span class="built_in">set</span> hive.exec.compress.intermediate=<span class="literal">true</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>开启mapreduce中map输出压缩功能</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">hive (<span class="keyword">default</span>)<span class="operator">&gt;</span><span class="keyword">set</span> mapreduce.map.output.compress<span class="operator">=</span><span class="literal">true</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>设置mapreduce中map输出数据的压缩方式</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt;<span class="built_in">set</span> mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>执行查询语句</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; select count(ename) name from emp;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="14-2-开启Reduce输出阶段压缩"><a href="#14-2-开启Reduce输出阶段压缩" class="headerlink" title="14.2 开启Reduce输出阶段压缩"></a>14.2 开启Reduce输出阶段压缩</h2><p><strong>开启hive最终输出数据压缩功能</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt;<span class="built_in">set</span> hive.exec.compress.output=<span class="literal">true</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>开启mapreduce最终输出数据压缩</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt;<span class="built_in">set</span> mapreduce.output.fileoutputformat.compress=<span class="literal">true</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>设置mapreduce最终数据输出压缩方式</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; <span class="built_in">set</span> mapreduce.output.fileoutputformat.compress.codec =</span><br><span class="line">org.apache.hadoop.io.compress.SnappyCodec;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>设置mapreduce最终数据输出压缩为块压缩</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; <span class="built_in">set</span> mapreduce.output.fileoutputformat.compress.type=BLOCK;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>测试一下输出结果是否是压缩文件</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">hive (default)&gt; insert overwrite <span class="built_in">local</span> directory</span><br><span class="line"><span class="string">&#x27;/home/lytdev/distribute-result&#x27;</span> select * from emp distribute by deptno sort by empno desc;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="14-3-文件存储格式"><a href="#14-3-文件存储格式" class="headerlink" title="14.3 文件存储格式"></a>14.3 文件存储格式</h2><p>Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET</p>
<h2 id="14-4-列式存储和行式存储"><a href="#14-4-列式存储和行式存储" class="headerlink" title="14.4 列式存储和行式存储"></a>14.4 列式存储和行式存储</h2><ul>
<li>  行存储的特点</li>
</ul>
<p>查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。</p>
<ul>
<li>  列存储的特点</li>
</ul>
<p>因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。</p>
<ul>
<li>  TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；</li>
<li>  ORC和PARQUET是基于列式存储的；</li>
<li>  ORC常用于MapReduce，PARQUET常用于spark。</li>
</ul>
<h2 id="14-5-存储和压缩结合"><a href="#14-5-存储和压缩结合" class="headerlink" title="14.5 存储和压缩结合"></a>14.5 存储和压缩结合</h2><blockquote>
<p>创建一个非压缩的的ORC存储方式</p>
</blockquote>
<p><strong>创建一个orc表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> log_orc_none(</span><br><span class="line">track_time string,</span><br><span class="line">url string,</span><br><span class="line">session_id string,</span><br><span class="line">referer string,</span><br><span class="line">ip string,</span><br><span class="line">end_user_id string,</span><br><span class="line">city_id string</span><br><span class="line">)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">stored <span class="keyword">as</span> orc tblproperties (&quot;orc.compress&quot;<span class="operator">=</span>&quot;NONE&quot;);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>插入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">table</span> log_orc <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> log_text;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查看表中数据大小</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">dfs <span class="operator">-</span>du <span class="operator">-</span>h <span class="operator">/</span><span class="keyword">user</span><span class="operator">/</span>hive<span class="operator">/</span>warehouse<span class="operator">/</span>log_orc<span class="operator">/</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<blockquote>
<p>创建一个SNAPPY压缩的ORC存储方式</p>
</blockquote>
<p><strong>创建一个orc表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> log_orc_snappy(</span><br><span class="line">track_time string,</span><br><span class="line">url string,</span><br><span class="line">session_id string,</span><br><span class="line">referer string,</span><br><span class="line">ip string,</span><br><span class="line">end_user_id string,</span><br><span class="line">city_id string</span><br><span class="line">)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">stored <span class="keyword">as</span> orc tblproperties (&quot;orc.compress&quot;<span class="operator">=</span>&quot;SNAPPY&quot;);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>插入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">table</span> log_orc_snappy <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> log_text;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>查看表中数据大小</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">dfs <span class="operator">-</span>du <span class="operator">-</span>h <span class="operator">/</span><span class="keyword">user</span><span class="operator">/</span>hive<span class="operator">/</span>warehouse<span class="operator">/</span>log_orc_snappy<span class="operator">/</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>**存储方式和压缩总结  **</p>
<blockquote>
<p>在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo</p>
</blockquote>
<h1 id="十五、企业级调优"><a href="#十五、企业级调优" class="headerlink" title="十五、企业级调优"></a>十五、企业级调优</h1><h2 id="15-1-Fetch抓取"><a href="#15-1-Fetch抓取" class="headerlink" title="15.1 Fetch抓取"></a>15.1 Fetch抓取</h2><ul>
<li>  <strong>Fetch抓取</strong></li>
</ul>
<p>Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。</p>
<ul>
<li>  <strong>Fetch参数配置</strong></li>
</ul>
<figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">name</span>&gt;</span>hive.fetch.task.conversion<span class="tag">&lt;/<span class="name">name</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">value</span>&gt;</span>more<span class="tag">&lt;/<span class="name">value</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">description</span>&gt;</span></span><br><span class="line">	Expects one of [none, minimal, more].</span><br><span class="line">	Some select queries can be converted to single FETCH task minimizing latency.</span><br><span class="line">	Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins.</span><br><span class="line">	0. none : disable hive.fetch.task.conversion</span><br><span class="line">	1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only</span><br><span class="line">	2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)</span><br><span class="line">	<span class="tag">&lt;/<span class="name">description</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>案例实操1</strong><br>把hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.fetch.task.conversion=none;</span><br><span class="line">select * from emp;</span><br><span class="line">select ename from emp;</span><br><span class="line">select ename from emp <span class="built_in">limit</span> 3;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>案例实操2</strong><br>把hive.fetch.task.conversion设置成more，然后执行查询语句，如下查询方式都不会执行mapreduce程序</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.fetch.task.conversion=more;</span><br><span class="line">select * from emp;</span><br><span class="line">select ename from emp;</span><br><span class="line">select ename from emp <span class="built_in">limit</span> 3;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-2-本地模式"><a href="#15-2-本地模式" class="headerlink" title="15.2 本地模式"></a>15.2 本地模式</h2><p><strong>意义</strong><br>Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短<br><strong>开启本地模式</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 开启本地mr</span></span><br><span class="line"><span class="built_in">set</span> hive.exec.mode.local.auto=<span class="literal">true</span>;</span><br><span class="line"><span class="comment"># 设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M</span></span><br><span class="line"><span class="built_in">set</span> hive.exec.mode.local.auto.inputbytes.max=50000000;</span><br><span class="line"><span class="comment"># 设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4</span></span><br><span class="line"><span class="built_in">set</span> hive.exec.mode.local.auto.input.files.max=10;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-3-小表、大表join"><a href="#15-3-小表、大表join" class="headerlink" title="15.3 小表、大表join"></a>15.3 小表、大表join</h2><p>新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。</p>
<h3 id="15-3-1-需求"><a href="#15-3-1-需求" class="headerlink" title="15.3.1 需求"></a>15.3.1 需求</h3><p>测试大表join小表和小表join大表的效率</p>
<h3 id="15-3-2-建大表、小表和join大表的语句"><a href="#15-3-2-建大表、小表和join大表的语句" class="headerlink" title="15.3.2 建大表、小表和join大表的语句"></a>15.3.2 建大表、小表和join大表的语句</h3><p><strong>（1）创建大表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> bigtable(id <span class="type">bigint</span>, <span class="type">time</span> <span class="type">bigint</span>, uid string, keyword string, url_rank <span class="type">int</span>, click_num <span class="type">int</span>, click_url string) <span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（2）创建小表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> smalltable(id <span class="type">bigint</span>, <span class="type">time</span> <span class="type">bigint</span>, uid string, keyword string, url_rank <span class="type">int</span>, click_num <span class="type">int</span>, click_url string) <span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（3）创建join后的表</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> jointable(id <span class="type">bigint</span>, <span class="type">time</span> <span class="type">bigint</span>, uid string, keyword string, url_rank <span class="type">int</span>, click_num <span class="type">int</span>, click_url string) <span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（4）导入数据</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/home/lytdev/bigtable&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> bigtable;</span><br><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/home/lytdev/smalltable&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> smalltable;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（5）关闭mapjoin功能（默认是打开的）</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.auto.convert.join = <span class="literal">false</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（6）执行小表JOIN大表语句</strong></p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">table</span> jointable</span><br><span class="line"><span class="keyword">select</span> b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url</span><br><span class="line"><span class="keyword">from</span> smalltable s</span><br><span class="line"><span class="keyword">join</span> bigtable b</span><br><span class="line"><span class="keyword">on</span> b.id <span class="operator">=</span> s.id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（7）执行结果</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">MapReduce Total cumulative CPU time: 31 seconds 100 msec</span><br><span class="line">No rows affected (52.897 seconds)</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（8）执行大表JOIN小表语句</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">insert overwrite table jointable</span><br><span class="line">select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url</span><br><span class="line">from bigtable b</span><br><span class="line">join smalltable s</span><br><span class="line">on s.id = b.id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（9）执行结果</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">MapReduce Total cumulative CPU time: 29 seconds 790 msec</span><br><span class="line">No rows affected (50.443 seconds)</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p><strong>（10）注意</strong><br>大表放在左边 left join 小表，可以走mapjoin进行优化；<br>如果使用 join，也就是inner join 大表小表的左右顺序无所谓，都会进行优化</p>
<h2 id="15-4-大表和大表join"><a href="#15-4-大表和大表join" class="headerlink" title="15.4 大表和大表join"></a>15.4 大表和大表join</h2><h3 id="15-4-1-空key过滤"><a href="#15-4-1-空key过滤" class="headerlink" title="15.4.1 空key过滤"></a>15.4.1 空key过滤</h3><p>进行空值过滤时，放在子查询中</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">table</span> jointable <span class="keyword">select</span> n.<span class="operator">*</span> <span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> nullidtable <span class="keyword">where</span> id <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span> ) n <span class="keyword">left</span> <span class="keyword">join</span> ori o <span class="keyword">on</span> n.id <span class="operator">=</span> o.id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="15-4-2-空key替换"><a href="#15-4-2-空key替换" class="headerlink" title="15.4.2 空key替换"></a>15.4.2 空key替换</h3><p>有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上，避免数据倾斜，数据倾斜会导致MapTask或者ReduceTask执行不了，从而导致整个MR任务执行不了。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">table</span> jointable</span><br><span class="line"><span class="keyword">select</span> n.<span class="operator">*</span> <span class="keyword">from</span> nullidtable n <span class="keyword">full</span> <span class="keyword">join</span> ori o <span class="keyword">on</span></span><br><span class="line"><span class="keyword">case</span> <span class="keyword">when</span> n.id <span class="keyword">is</span> <span class="keyword">null</span> <span class="keyword">then</span> concat(<span class="string">&#x27;hive&#x27;</span>, rand()) <span class="keyword">else</span> n.id <span class="keyword">end</span> <span class="operator">=</span> o.id;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-5-开启mapjoin参数配置"><a href="#15-5-开启mapjoin参数配置" class="headerlink" title="15.5 开启mapjoin参数配置"></a>15.5 开启mapjoin参数配置</h2><ul>
<li>  设置自动选择Mapjoin</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">set hive.auto.convert.join = true; #默认为true</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  大表小表的阈值设置（默认25M一下认为是小表）</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.mapjoin.smalltable.filesize=25000000</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>当服务器内容256GB时，可以增大该参数配置</p>
<h2 id="15-6-group-by"><a href="#15-6-group-by" class="headerlink" title="15.6 group by"></a>15.6 group by</h2><p>默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。</p>
<ul>
<li>  是否在Map端进行聚合，默认为True</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.map.aggr = <span class="literal">true</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  在Map端进行聚合操作的条目数目</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.groupby.mapaggr.checkinterval = 100000</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  有数据倾斜的时候进行负载均衡（默认是false）</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.groupby.skewindata = <span class="literal">true</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-7-Count-Distinct-去重统计"><a href="#15-7-Count-Distinct-去重统计" class="headerlink" title="15.7 Count(Distinct) 去重统计"></a>15.7 Count(Distinct) 去重统计</h2><p>数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT的全聚合操作，即使设定了reduce task个数，set mapred.reduce.tasks=100；hive也只会启动一个reducer。这就造成一个Reduce处理的数据量太大，导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"># <span class="built_in">count</span>(<span class="keyword">distinct</span>)方式</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="keyword">distinct</span> id) <span class="keyword">from</span> bigtable;</span><br><span class="line"># <span class="keyword">group</span> <span class="keyword">by</span>方式，必须先设置reduce数量，否则也是默认一个reduce</span><br><span class="line"><span class="keyword">set</span> mapreduce.job.reduces <span class="operator">=</span> <span class="number">5</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(id) <span class="keyword">from</span> (<span class="keyword">select</span> id <span class="keyword">from</span> bigtable <span class="keyword">group</span> <span class="keyword">by</span> id) a;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-8-MR优化"><a href="#15-8-MR优化" class="headerlink" title="15.8 MR优化"></a>15.8 MR优化</h2><ul>
<li>  合理设置Map数</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="built_in">set</span> hive.map.aggr = <span class="literal">true</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  开启小文件合并</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># CombineHiveInputFormat具有对小文件进行合并的功能</span></span><br><span class="line"><span class="built_in">set</span> hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;</span><br><span class="line"><span class="comment"># 在map-only任务结束时合并小文件，默认true</span></span><br><span class="line"><span class="built_in">set</span> hive.merge.mapfiles = <span class="literal">true</span>;</span><br><span class="line"><span class="comment"># 在map-reduce任务结束时合并小文件，默认false</span></span><br><span class="line"><span class="built_in">set</span> hive.merge.mapredfiles = <span class="literal">true</span>;</span><br><span class="line"><span class="comment"># 合并文件的大小，默认256M</span></span><br><span class="line"><span class="built_in">set</span> hive.merge.size.per.task = 268435456;</span><br><span class="line"><span class="comment"># 当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge</span></span><br><span class="line"><span class="built_in">set</span> hive.merge.smallfiles.avgsize = 16777216;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<ul>
<li>  合理设置Reduce数</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 调整reduce个数方法一</span></span><br><span class="line"><span class="comment"># 每个Reduce处理的数据量默认是256MB</span></span><br><span class="line">hive.exec.reducers.bytes.per.reducer=256000000</span><br><span class="line"><span class="comment"># 每个任务最大的reduce数，默认为1009</span></span><br><span class="line">hive.exec.reducers.max=1009</span><br><span class="line"><span class="comment"># 计算reducer数的公式</span></span><br><span class="line">N=min(参数2，总输入数据量/参数1)</span><br><span class="line"><span class="comment"># 调整reduce个数方法二</span></span><br><span class="line"><span class="comment"># 在hadoop的mapred-default.xml文件中修改，设置每个job的Reduce个数</span></span><br><span class="line"><span class="built_in">set</span> mapreduce.job.reduces = 15;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-9-开启并行执行"><a href="#15-9-开启并行执行" class="headerlink" title="15.9 开启并行执行"></a>15.9 开启并行执行</h2><ul>
<li>  设置任务并行度</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 打开任务并行执行</span></span><br><span class="line"><span class="built_in">set</span> hive.exec.parallel=<span class="literal">true</span>;</span><br><span class="line"><span class="comment"># 同一个sql允许最大并行度，默认为8。</span></span><br><span class="line"><span class="built_in">set</span> hive.exec.parallel.thread.number=16;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-10-JVM重用"><a href="#15-10-JVM重用" class="headerlink" title="15.10 JVM重用"></a>15.10 JVM重用</h2><p>JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短</p>
<figure class="highlight plaintext"><table><tr><td class="code"><pre><span class="line">&lt;property&gt;</span><br><span class="line">	&lt;name&gt;mapreduce.job.jvm.numtasks&lt;/name&gt;</span><br><span class="line">	&lt;value&gt;10&lt;/value&gt;</span><br><span class="line">	&lt;description&gt;How many tasks to run per jvm. If set to -1, there is</span><br><span class="line">	no limit.</span><br><span class="line">	&lt;/description&gt;</span><br><span class="line">&lt;/property&gt;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="15-11-推测执行"><a href="#15-11-推测执行" class="headerlink" title="15.11 推测执行"></a>15.11 推测执行</h2><p>在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。</p>
<figure class="highlight xml"><table><tr><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">name</span>&gt;</span>mapreduce.map.speculative<span class="tag">&lt;/<span class="name">name</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">value</span>&gt;</span>true<span class="tag">&lt;/<span class="name">value</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line"></span><br></pre></td></tr></table></figure></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined" rel="external nofollow noreferrer">liuyuantao</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://liuyuantao.gitee.io/2021/11/13/3f0a31ee0039.html">https://liuyuantao.gitee.io/2021/11/13/3f0a31ee0039.html</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow noreferrer" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://liuyuantao.gitee.io" target="_blank">全栈进阶那些事</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/Hadoop/">Hadoop</a><a class="post-meta__tags" href="/tags/Hive/">Hive</a></div><div class="post_share"><div class="social-share" data-image="https://img-blog.csdnimg.cn/20200529093005305.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/qr_code_wechat.jpg" target="_blank"><img class="post-qr-code-img" src="/img/qr_code_wechat.jpg" alt="微信"/></a><div class="post-qr-code-desc">微信</div></li><li class="reward-item"><a href="/img/qr_code_alipay.jpg" target="_blank"><img class="post-qr-code-img" src="/img/qr_code_alipay.jpg" alt="支付宝"/></a><div class="post-qr-code-desc">支付宝</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2021/11/18/fec2d399fcb4.html"><img class="prev-cover" src="https://upload-images.jianshu.io/upload_images/195230-462138b5b8e6e815.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">物化视图简介与ClickHouse中的应用示例</div></div></a></div><div class="next-post pull-right"><a href="/2021/11/12/56ac102284d9.html"><img class="next-cover" src="https://oscimg.oschina.net/oscnet/20200617165336828.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">深入浅出学习MapReduce</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/2020/11/07/ec88b39f0ddd.html" title="安装Hive3.1.2"><img class="cover" src="https://cdn.pixabay.com/photo/2021/10/14/13/50/book-6709160__340.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">安装Hive3.1.2</div></div></a></div><div><a href="/2020/11/07/fed9c14bf1ec.html" title="记一次HiveServer2启动不起来问题"><img class="cover" src="https://oscimg.oschina.net/oscnet/up-d53b671bdbcd201845e0f686b5057858118.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">记一次HiveServer2启动不起来问题</div></div></a></div><div><a href="/2020/11/07/7d14a19c4606.html" title="DataNode工作机制"><img class="cover" src="https://oscimg.oschina.net/oscnet/up-0a831a2854d1859bc355a98a741ee74452b.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">DataNode工作机制</div></div></a></div><div><a href="/2020/11/07/9cc4868ea813.html" title="Hadoop-Yarn常用的调优参数"><img class="cover" src="https://cdn.pixabay.com/photo/2021/08/08/10/34/ocean-6530523__340.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">Hadoop-Yarn常用的调优参数</div></div></a></div><div><a href="/2020/11/07/5f9b1cc664c1.html" title="HDFS—集群扩容及缩容"><img class="cover" src="https://oscimg.oschina.net/oscnet/up-acb08d98e431429592cdb8802409bb29a19.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">HDFS—集群扩容及缩容</div></div></a></div><div><a href="/2020/11/07/a8fda6be4c8d.html" title="HDFS—存储优化（纠删码）"><img class="cover" src="https://oscimg.oschina.net/oscnet/up-87ba27811580330155a29254c76223dd59f.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-11-07</div><div class="title">HDFS—存储优化（纠删码）</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B8%80%E3%80%81Hive%E7%9A%84%E7%AE%80%E4%BB%8B"><span class="toc-text">一、Hive的简介</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-1-Hive%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5"><span class="toc-text">1.1 Hive基本概念</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#1-2-Hive%E4%BC%98%E7%BC%BA%E7%82%B9"><span class="toc-text">1.2 Hive优缺点</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#1-3-Hive%E6%9E%B6%E6%9E%84"><span class="toc-text">1.3 Hive架构</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#1-4-Hive%E5%B7%A5%E4%BD%9C%E6%9C%BA%E5%88%B6"><span class="toc-text">1.4 Hive工作机制</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#1-5-Hive%E5%92%8C%E6%95%B0%E6%8D%AE%E5%BA%93%E6%AF%94%E8%BE%83"><span class="toc-text">1.5 Hive和数据库比较</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%BA%8C%E3%80%81Hive%E7%9A%84%E5%AE%89%E8%A3%85"><span class="toc-text">二、Hive的安装</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#2-1-Hive%E4%B8%8B%E8%BD%BD"><span class="toc-text">2.1 Hive下载</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-2-Hive%E8%A7%A3%E5%8E%8B"><span class="toc-text">2.2 Hive解压</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-3-%E9%85%8D%E7%BD%AE%E7%8E%AF%E5%A2%83%E5%8F%98%E9%87%8F"><span class="toc-text">2.3 配置环境变量</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-5-%E5%90%AF%E5%8A%A8%E5%B9%B6%E6%B5%8B%E8%AF%95hive"><span class="toc-text">2.5 启动并测试hive</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-6-hive%E7%9A%84bug"><span class="toc-text">2.6 hive的bug</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-7-MySQL%E7%9A%84%E5%AE%89%E8%A3%85"><span class="toc-text">2.7 MySQL的安装</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-8-Hive%E9%85%8D%E7%BD%AEMySQL"><span class="toc-text">2.8 Hive配置MySQL</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-9-%E5%90%AF%E5%8A%A8Hive"><span class="toc-text">2.9 启动Hive</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-10-Beeline%E5%90%AF%E5%8A%A8Hive"><span class="toc-text">2.10 Beeline启动Hive</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B8%89%E3%80%81Hive%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-text">三、Hive的使用</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#3-1-Hive%E7%9A%84%E4%BA%A4%E4%BA%92%E5%91%BD%E4%BB%A4"><span class="toc-text">3.1 Hive的交互命令</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-2-Hive%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E4%BD%8D%E7%BD%AE%E9%85%8D%E7%BD%AE"><span class="toc-text">3.2 Hive数据仓库位置配置</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-3-%E6%9F%A5%E8%AF%A2%E5%90%8E%E4%BF%A1%E6%81%AF%E6%98%BE%E7%A4%BA%E9%85%8D%E7%BD%AE%EF%BC%88%E5%8F%AF%E9%80%89%E9%85%8D%EF%BC%89"><span class="toc-text">3.3 查询后信息显示配置（可选配）</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-4-Hive%E8%BF%90%E8%A1%8C%E6%97%A5%E5%BF%97%E4%BF%A1%E6%81%AF%E9%85%8D%E7%BD%AE%EF%BC%88%E5%BF%85%E9%A1%BB%E9%85%8D%E7%BD%AE%EF%BC%89"><span class="toc-text">3.4 Hive运行日志信息配置（必须配置）</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%9B%9B%E3%80%81Hive%E7%9A%84%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-text">四、Hive的数据类型</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#4-1-Hive%E5%9F%BA%E6%9C%AC%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-text">4.1 Hive基本数据类型</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-2-Hive%E9%9B%86%E5%90%88%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-text">4.2 Hive集合数据类型</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-3-%E7%B1%BB%E5%9E%8B%E8%BD%AC%E5%8C%96"><span class="toc-text">4.3 类型转化</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%BA%94%E3%80%81DDL%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80"><span class="toc-text">五、DDL数据库定义语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#5-1-%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.1 创建数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-2-%E6%98%BE%E7%A4%BA%E6%89%80%E6%9C%89%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.2 显示所有数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-3-%E8%BF%87%E6%BB%A4%E6%98%BE%E7%A4%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.3 过滤显示数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-4-%E6%98%BE%E7%A4%BA%E6%8C%87%E5%AE%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E4%BF%A1%E6%81%AF"><span class="toc-text">5.4 显示指定数据库的信息</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-5-%E6%98%BE%E7%A4%BA%E6%8C%87%E5%AE%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E8%AF%A6%E7%BB%86%E4%BF%A1%E6%81%AF"><span class="toc-text">5.5 显示指定数据库的详细信息</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-6-%E5%88%87%E6%8D%A2%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.6 切换数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-7-%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.7 修改数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-8-%E5%88%A0%E9%99%A4%E7%A9%BA%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.8 删除空的数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-9-%E5%88%A0%E9%99%A4%E9%9D%9E%E7%A9%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-text">5.9 删除非空数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-10-%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E8%A1%A8"><span class="toc-text">5.10 创建数据表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-11-%E6%98%BE%E7%A4%BA%E6%8C%87%E5%AE%9A%E8%A1%A8%E7%9A%84%E4%BF%A1%E6%81%AF%E5%92%8C%E5%88%9B%E5%BB%BA%E8%A1%A8%E6%97%B6%E7%9A%84%E9%85%8D%E7%BD%AE"><span class="toc-text">5.11 显示指定表的信息和创建表时的配置</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-12-%E6%98%BE%E7%A4%BA%E6%8C%87%E5%AE%9A%E8%A1%A8%E7%9A%84%E8%AF%A6%E7%BB%86%E4%BF%A1%E6%81%AF"><span class="toc-text">5.12 显示指定表的详细信息</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-13-%E5%A4%96%E9%83%A8%E8%A1%A8%E5%92%8C%E7%AE%A1%E7%90%86%E8%A1%A8"><span class="toc-text">5.13 外部表和管理表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-14-%E5%B0%86%E6%95%B0%E6%8D%AE%E8%A1%A8%E4%BF%AE%E6%94%B9%E4%B8%BA%E5%A4%96%E9%83%A8%E8%A1%A8"><span class="toc-text">5.14 将数据表修改为外部表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-15-%E5%B0%86%E6%95%B0%E6%8D%AE%E8%A1%A8%E4%BF%AE%E6%94%B9%E4%B8%BA%E7%AE%A1%E7%90%86%E8%A1%A8"><span class="toc-text">5.15 将数据表修改为管理表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-16-%E5%88%9B%E5%BB%BA%E5%88%86%E5%8C%BA%E8%A1%A8"><span class="toc-text">5.16 创建分区表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-17-%E6%96%B0%E5%A2%9E%E5%88%86%E5%8C%BA"><span class="toc-text">5.17 新增分区</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-18-%E5%88%A0%E9%99%A4%E5%88%86%E5%8C%BA"><span class="toc-text">5.18 删除分区</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-19-%E6%9F%A5%E8%AF%A2%E5%88%86%E5%8C%BA%E8%A1%A8%E6%9C%89%E5%A4%9A%E5%B0%91%E5%88%86%E5%8C%BA"><span class="toc-text">5.19 查询分区表有多少分区</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-20-%E6%9F%A5%E8%AF%A2%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E7%BB%93%E6%9E%84"><span class="toc-text">5.20 查询分区表的结构</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-21-%E5%88%9B%E5%BB%BA%E4%BA%8C%E7%BA%A7%E5%88%86%E5%8C%BA%E8%A1%A8"><span class="toc-text">5.21 创建二级分区表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-22-%E4%BF%AE%E5%A4%8D%E5%88%86%E5%8C%BA"><span class="toc-text">5.22 修复分区</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-23-%E9%87%8D%E5%91%BD%E5%90%8D%E8%A1%A8"><span class="toc-text">5.23 重命名表</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-24-%E6%B7%BB%E5%8A%A0%E5%88%97"><span class="toc-text">5.24 添加列</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-25-%E6%9B%B4%E6%96%B0%E5%88%97"><span class="toc-text">5.25 更新列</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-26-%E6%9B%BF%E6%8D%A2%E5%88%97"><span class="toc-text">5.26 替换列</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%85%AD%E3%80%81DML%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C%E8%AF%AD%E8%A8%80"><span class="toc-text">六、DML数据库操作语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#6-1-%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%85%A5"><span class="toc-text">6.1 数据导入</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-1-%E5%90%91%E8%A1%A8%E4%B8%AD%E8%A3%85%E8%BD%BD%E6%95%B0%E6%8D%AE"><span class="toc-text">6.1.1 向表中装载数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-2-%E9%80%9A%E8%BF%87%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E5%90%91%E8%A1%A8%E4%B8%AD%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="toc-text">6.1.2 通过查询语句向表中插入数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-3-%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E4%B8%AD%E5%88%9B%E5%BB%BA%E8%A1%A8%E5%B9%B6%E5%8A%A0%E8%BD%BD%E6%95%B0%E6%8D%AE%EF%BC%88As-Select%EF%BC%89"><span class="toc-text">6.1.3 查询语句中创建表并加载数据（As Select）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-4-%E5%88%9B%E5%BB%BA%E8%A1%A8%E6%97%B6%E9%80%9A%E8%BF%87Location%E6%8C%87%E5%AE%9A%E5%8A%A0%E8%BD%BD%E6%95%B0%E6%8D%AE%E8%B7%AF%E5%BE%84"><span class="toc-text">6.1.4 创建表时通过Location指定加载数据路径</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-5-Export%E5%AF%BC%E5%87%BA%E5%88%B0HDFS%E4%B8%8A"><span class="toc-text">6.1.5 Export导出到HDFS上</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-2-%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%87%BA"><span class="toc-text">6.2 数据导出</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#6-2-1-Insert%E5%AF%BC%E5%87%BA"><span class="toc-text">6.2.1 Insert导出</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-2-2-Hadoop%E5%91%BD%E4%BB%A4%E5%AF%BC%E5%87%BA%E5%88%B0%E6%9C%AC%E5%9C%B0"><span class="toc-text">6.2.2 Hadoop命令导出到本地</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-2-3-Hive-Shell-%E5%91%BD%E4%BB%A4%E5%AF%BC%E5%87%BA"><span class="toc-text">6.2.3 Hive Shell 命令导出</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-2-4-Export%E5%AF%BC%E5%87%BA%E5%88%B0HDFS%E4%B8%8A"><span class="toc-text">6.2.4 Export导出到HDFS上</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-3-%E6%B8%85%E9%99%A4%E6%95%B0%E6%8D%AE%EF%BC%88%E4%B8%8D%E6%B8%85%E9%99%A4%E5%85%83%E6%95%B0%E6%8D%AE%EF%BC%89"><span class="toc-text">6.3 清除数据（不清除元数据）</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B8%83%E3%80%81%E5%9F%BA%E6%9C%AC%E6%9F%A5%E8%AF%A2"><span class="toc-text">七、基本查询</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%85%AB%E3%80%81%E5%88%86%E7%BB%84"><span class="toc-text">八、分组</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#8-1-group-by%E8%AF%AD%E5%8F%A5"><span class="toc-text">8.1 group by语句</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#8-2-having%E8%AF%AD%E5%8F%A5"><span class="toc-text">8.2 having语句</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B9%9D%E3%80%81join%E8%AF%AD%E5%8F%A5"><span class="toc-text">九、join语句</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#9-1-%E7%AD%89%E5%80%BCjoin"><span class="toc-text">9.1 等值join</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-2-%E8%A1%A8%E7%9A%84%E5%88%AB%E5%90%8D"><span class="toc-text">9.2 表的别名</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-3-%E5%86%85%E8%BF%9E%E6%8E%A5"><span class="toc-text">9.3 内连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-4-%E5%B7%A6%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-text">9.4 左外连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-5-%E5%8F%B3%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-text">9.5 右外连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-6-%E6%BB%A1%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-text">9.6 满外连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-7-%E5%A4%9A%E8%A1%A8%E8%BF%9E%E6%8E%A5"><span class="toc-text">9.7 多表连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-8-%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF"><span class="toc-text">9.8 笛卡尔积</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-9-%E8%BF%9E%E6%8E%A5%E8%B0%93%E8%AF%8D%E4%B8%AD%E4%B8%8D%E6%94%AF%E6%8C%81or"><span class="toc-text">9.9 连接谓词中不支持or</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E3%80%81%E6%8E%92%E5%BA%8F"><span class="toc-text">十、排序</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#10-1-%E5%85%A8%E5%B1%80%E6%8E%92%E5%BA%8F"><span class="toc-text">10.1 全局排序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-2-%E6%8C%89%E7%85%A7%E5%88%AB%E5%90%8D%E6%8E%92%E5%BA%8F"><span class="toc-text">10.2 按照别名排序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-3-%E5%A4%9A%E4%B8%AA%E5%88%97%E6%8E%92%E5%BA%8F"><span class="toc-text">10.3 多个列排序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-4-%E6%AF%8F%E4%B8%AAMapReduce%E5%86%85%E9%83%A8%E6%8E%92%E5%BA%8F"><span class="toc-text">10.4 每个MapReduce内部排序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-5-%E5%88%86%E5%8C%BA%E6%8E%92%E5%BA%8F"><span class="toc-text">10.5 分区排序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-6-Cluster-By"><span class="toc-text">10.6 Cluster By</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E4%B8%80%E3%80%81%E5%88%86%E6%A1%B6%E5%8F%8A%E6%8A%BD%E6%A0%B7%E6%9F%A5%E8%AF%A2"><span class="toc-text">十一、分桶及抽样查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#11-1-%E5%88%86%E6%A1%B6%E8%A1%A8%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8"><span class="toc-text">11.1 分桶表数据存储</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#11-2-%E5%88%86%E6%A1%B6%E6%8A%BD%E6%A0%B7%E6%9F%A5%E8%AF%A2"><span class="toc-text">11.2 分桶抽样查询</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E4%BA%8C%E3%80%81%E5%85%B6%E4%BB%96%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2%E5%87%BD%E6%95%B0"><span class="toc-text">十二、其他常用查询函数</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#12-1-%E7%A9%BA%E5%AD%97%E6%AE%B5%E8%B5%8B%E5%80%BC"><span class="toc-text">12.1 空字段赋值</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-2-%E6%97%B6%E9%97%B4%E7%B1%BB"><span class="toc-text">12.2 时间类</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-3-CASE-WHEN"><span class="toc-text">12.3 CASE WHEN</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-4-%E8%A1%8C%E8%BD%AC%E5%88%97"><span class="toc-text">12.4 行转列</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-5-%E5%88%97%E8%BD%AC%E8%A1%8C"><span class="toc-text">12.5 列转行</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-6-%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0"><span class="toc-text">12.6 窗口函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-7-Rank"><span class="toc-text">12.7 Rank</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-8-%E7%B3%BB%E7%BB%9F%E5%87%BD%E6%95%B0"><span class="toc-text">12.8 系统函数</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E4%B8%89%E3%80%81%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0UDF"><span class="toc-text">十三、自定义函数UDF</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#13-1-%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0%E7%B1%BB%E5%9E%8B"><span class="toc-text">13.1 自定义函数类型</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-2-%E5%88%9B%E5%BB%BA%E9%A1%B9%E7%9B%AE%E5%AF%BC%E5%85%A5%E4%BE%9D%E8%B5%96"><span class="toc-text">13.2 创建项目导入依赖</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-3-%E5%88%9B%E5%BB%BA%E4%B8%80%E4%B8%AA%E7%B1%BB%E7%BB%A7%E6%89%BF%E4%B8%8EUDF"><span class="toc-text">13.3 创建一个类继承与UDF</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-4-%E6%89%93%E6%88%90jar%E5%8C%85%EF%BC%8C%E5%B9%B6%E4%B8%8A%E4%BC%A0%E9%9B%86%E7%BE%A4"><span class="toc-text">13.4 打成jar包，并上传集群</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-5-%E4%B8%B4%E6%97%B6%E4%B8%8A%E4%BC%A0jar%E5%8C%85%E8%87%B3hive%EF%BC%8C%E9%80%80%E5%87%BA%E6%97%B6%E5%A4%B1%E6%95%88"><span class="toc-text">13.5 临时上传jar包至hive，退出时失效</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-6-%E5%88%9B%E5%BB%BA%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0"><span class="toc-text">13.6 创建自定义函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-7-%E6%B5%8B%E8%AF%95%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0"><span class="toc-text">13.7 测试自定义函数</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E5%9B%9B%E3%80%81%E5%8E%8B%E7%BC%A9%E4%B8%8E%E5%AD%98%E5%82%A8"><span class="toc-text">十四、压缩与存储</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#14-1-%E5%BC%80%E5%90%AFMap%E8%BE%93%E5%87%BA%E9%98%B6%E6%AE%B5%E5%8E%8B%E7%BC%A9"><span class="toc-text">14.1 开启Map输出阶段压缩</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#14-2-%E5%BC%80%E5%90%AFReduce%E8%BE%93%E5%87%BA%E9%98%B6%E6%AE%B5%E5%8E%8B%E7%BC%A9"><span class="toc-text">14.2 开启Reduce输出阶段压缩</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#14-3-%E6%96%87%E4%BB%B6%E5%AD%98%E5%82%A8%E6%A0%BC%E5%BC%8F"><span class="toc-text">14.3 文件存储格式</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#14-4-%E5%88%97%E5%BC%8F%E5%AD%98%E5%82%A8%E5%92%8C%E8%A1%8C%E5%BC%8F%E5%AD%98%E5%82%A8"><span class="toc-text">14.4 列式存储和行式存储</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#14-5-%E5%AD%98%E5%82%A8%E5%92%8C%E5%8E%8B%E7%BC%A9%E7%BB%93%E5%90%88"><span class="toc-text">14.5 存储和压缩结合</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%8D%81%E4%BA%94%E3%80%81%E4%BC%81%E4%B8%9A%E7%BA%A7%E8%B0%83%E4%BC%98"><span class="toc-text">十五、企业级调优</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#15-1-Fetch%E6%8A%93%E5%8F%96"><span class="toc-text">15.1 Fetch抓取</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-2-%E6%9C%AC%E5%9C%B0%E6%A8%A1%E5%BC%8F"><span class="toc-text">15.2 本地模式</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-3-%E5%B0%8F%E8%A1%A8%E3%80%81%E5%A4%A7%E8%A1%A8join"><span class="toc-text">15.3 小表、大表join</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#15-3-1-%E9%9C%80%E6%B1%82"><span class="toc-text">15.3.1 需求</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#15-3-2-%E5%BB%BA%E5%A4%A7%E8%A1%A8%E3%80%81%E5%B0%8F%E8%A1%A8%E5%92%8Cjoin%E5%A4%A7%E8%A1%A8%E7%9A%84%E8%AF%AD%E5%8F%A5"><span class="toc-text">15.3.2 建大表、小表和join大表的语句</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-4-%E5%A4%A7%E8%A1%A8%E5%92%8C%E5%A4%A7%E8%A1%A8join"><span class="toc-text">15.4 大表和大表join</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#15-4-1-%E7%A9%BAkey%E8%BF%87%E6%BB%A4"><span class="toc-text">15.4.1 空key过滤</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#15-4-2-%E7%A9%BAkey%E6%9B%BF%E6%8D%A2"><span class="toc-text">15.4.2 空key替换</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-5-%E5%BC%80%E5%90%AFmapjoin%E5%8F%82%E6%95%B0%E9%85%8D%E7%BD%AE"><span class="toc-text">15.5 开启mapjoin参数配置</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-6-group-by"><span class="toc-text">15.6 group by</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-7-Count-Distinct-%E5%8E%BB%E9%87%8D%E7%BB%9F%E8%AE%A1"><span class="toc-text">15.7 Count(Distinct) 去重统计</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-8-MR%E4%BC%98%E5%8C%96"><span class="toc-text">15.8 MR优化</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-9-%E5%BC%80%E5%90%AF%E5%B9%B6%E8%A1%8C%E6%89%A7%E8%A1%8C"><span class="toc-text">15.9 开启并行执行</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-10-JVM%E9%87%8D%E7%94%A8"><span class="toc-text">15.10 JVM重用</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-11-%E6%8E%A8%E6%B5%8B%E6%89%A7%E8%A1%8C"><span class="toc-text">15.11 推测执行</span></a></li></ol></li></ol></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By liuyuantao</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener external nofollow noreferrer" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener external nofollow noreferrer" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div><div class="footer_custom_text">扫描二维码,关注我们!了解更多全栈技能!<br/><img style="width:100px;height:100px;" src="/img/qrcode_for_gh_da91cebd00cc_258.jpg" ></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">简</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="/js/tw_cn.js"></script><script src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module"></script><script src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script src="/js/search/local-search.js"></script><script>var preloader = {
  endLoading: () => {
    document.body.style.overflow = 'auto';
    document.getElementById('loading-box').classList.add("loaded")
  },
  initLoading: () => {
    document.body.style.overflow = '';
    document.getElementById('loading-box').classList.remove("loaded")

  }
}
window.addEventListener('load',preloader.endLoading())</script><div class="js-pjax"></div><script defer="defer" id="fluttering_ribbon" mobile="false" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/canvas-fluttering-ribbon.min.js"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>